iT邦幫忙

0

[筆記,MySQL]計算陣列中不重複數量

舜~ 2019-11-01 15:53:572248 瀏覽
  • 分享至 

  • xImage
  •  

因為遇到這篇問題,為了解決問題而撰寫此CountArrayDistinct方法。
關於本方法有任何更好的做法、可以提升效能或是任何建議,歡迎提出討論~~

-- ========================
-- CountArrayDistinct
-- 建立方法,計算陣列文字(ex."a,b,c,b")中不重複的數量
-- ArrStr VARCHAR(21845) 要進行處理的字串
-- pos VARCHAR(10) 分割字串
-- ===================

DELIMITER $$
DROP FUNCTION IF EXISTS CountArrayDistinct $$
CREATE FUNCTION CountArrayDistinct(ArrStr VARCHAR(21845),pos VARCHAR(10)) RETURNS int
BEGIN

SET @startIndex = 0;
SET @posIndex = 0;
set @result = 0;
-- 如果已經有臨時表了就不用重建
CREATE TEMPORARY TABLE IF NOT EXISTS `tmp_CountArrayDistinct_table`
(
    name VARCHAR(1000) NOT NULL
);
-- 清空臨時表避免上一次的殘留
delete from tmp_CountArrayDistinct_table;
  
SET @posIndex = LOCATE(pos,ArrStr);
if @posIndex > 0 then
    -- 依序加分割的字串項目加入臨時表(最後一個會被略過)
    while  @posIndex > 0 do
		-- 加入
		insert into tmp_CountArrayDistinct_table values(SUBSTRING(ArrStr,@startIndex+1,@posIndex-@startIndex-1));
		-- 處理下一個起始索引
		set @startIndex = @posIndex;
		-- 找下一個
		SET @posIndex = LOCATE(pos,ArrStr,@startIndex+1);
    end while;

    -- 將最後一個字串項目加入臨時表
    insert into tmp_CountArrayDistinct_table values(SUBSTRING_INDEX(ArrStr,pos,-1));
    -- 輸出不重複數量
    set @result = (select count(DISTINCT name ) from tmp_CountArrayDistinct_table where name!='' and name is not null);

ELSEIf  LENGTH(ArrStr)>0 THEN
    -- 有傳入文字,但沒找到分割字元
	set @result = 1;
else
    set @result = 0;
END IF;

RETURN @result;

END $$
DELIMITER ;

感恩ckp6250大大提供優化版本

SELECT
	count(1) 
FROM
	(
	SELECT
		@num := @num + 1,
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', @num ), ',',- 1 ) AS str 
	FROM
		information_schema.GLOBAL_STATUS a,
		( SELECT @num := 0 ) b 
	WHERE
		@num < CHAR_LENGTH( 'a,b,c,b,a,z' )- CHAR_LENGTH(REPLACE ( 'a,b,c,b,a,z', ',', '' ))+ 1
	GROUP BY str 
	) a

使用範例

select CountArrayDistinct( 'a,b,c,b,a,z' , ',' );
-- 輸出 4 

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 則留言

1
ckp6250
iT邦好手 1 級 ‧ 2019-11-01 17:40:53

又要開 temp table, 又要 delete 又要 insert , 您的效能,肯定不好。

我的姿勢醜一點,沒關係吧。

SELECT
	COUNT(*) 
FROM
	(
	SELECT
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 1 ), ',',- 1 ) AS str UNION
	SELECT
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 2 ), ',',- 1 ) UNION
	SELECT
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 3 ), ',',- 1 ) UNION
	SELECT
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 4 ), ',',- 1 ) UNION
	SELECT
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 5 ), ',',- 1 ) UNION
SELECT
	SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', 6 ), ',',- 1 )) a

原理如此,細節您自行變化。

看更多先前的回應...收起先前的回應...

可是長度不知道,那你這個 union 幾個,要怎樣決定?

ckp6250 iT邦好手 1 級 ‧ 2019-11-01 19:16:08 檢舉

我是提醒原理啦,樓主若是領略其義,就能自行變化。

不必用到 temp table, delete , insert 等等傷筋動骨的東東。

其實,任何應用環境都有其【可能限度】,若是我,真有可能這麼幹,總比寫一堆迴圈或函數變數要方便多了。

黑貓白貓論。

若嫌姿勢醜,那就改成迴圈和變數吧,至少可以達成樓主說的【提升效能】之要求。

舜~ iT邦高手 1 級 ‧ 2019-11-01 20:55:23 檢舉

抱歉小弟資質愚昧,ckp6250大大能再詳細指點嗎?
大大的意思是算出多少個分隔字串後(數量 = (原始長度-空字串取代後的長度)/分隔符的長度),動態組合出UNION起來的sql再執行??

ckp6250 iT邦好手 1 級 ‧ 2019-11-01 21:12:55 檢舉

動態組合出UNION起來的sql再執行
是可行方法,

  又或者,算出有幾個字串,寫個迴圈,個別取出字串,判斷有無重覆,沒有重覆的,就用變數累加1。

反正,您要的這個東西,根本不需要用到 table , insert , delete 等等耗能工具。

1
ckp6250
iT邦好手 1 級 ‧ 2019-11-01 21:33:40

一指搞定。

SELECT
	count(*) 
FROM
	(
	SELECT
		@num := @num + 1,
		SUBSTRING_INDEX( SUBSTRING_INDEX( 'a,b,c,b,a,z', ',', @num ), ',',- 1 ) AS str 
	FROM
		information_schema.GLOBAL_STATUS a,
		( SELECT @num := 0 ) b 
	WHERE
		@num < CHAR_LENGTH( 'a,b,c,b,a,z' )- CHAR_LENGTH(REPLACE ( 'a,b,c,b,a,z', ',', '' ))+ 1
	GROUP BY
	str 
	) a
看更多先前的回應...收起先前的回應...

MySQL 有變數可以用.
還有這招 information_schema.GLOBAL_STATUS
現在有的換到 performance_schema 了.
搭配靈活運用.蠻好的.

ckp6250 iT邦好手 1 級 ‧ 2019-11-01 22:34:49 檢舉

這是抄您的啊,忘了嗎?/images/emoticon/emoticon62.gif

我當然知道啊.https://ithelp.ithome.com.tw/upload/images/20191101/20050647L3TK8vKwIS.jpg

ckp6250 iT邦好手 1 級 ‧ 2019-11-02 05:48:34 檢舉

對了,這二天,積極試用 postgresql
發現它真的很強大。

請教,若以此題而言,
postgresql 會怎麼寫?

我要留言

立即登入留言