因為遇到這篇問題,為了解決問題而撰寫此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
又要開 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 幾個,要怎樣決定?
我是提醒原理啦,樓主若是領略其義,就能自行變化。
不必用到 temp table, delete , insert 等等傷筋動骨的東東。
其實,任何應用環境都有其【可能限度】,若是我,真有可能這麼幹,總比寫一堆迴圈或函數變數要方便多了。
黑貓白貓論。
若嫌姿勢醜,那就改成迴圈和變數吧,至少可以達成樓主說的【提升效能】之要求。
抱歉小弟資質愚昧,ckp6250大大能再詳細指點嗎?
大大的意思是算出多少個分隔字串後(數量 = (原始長度-空字串取代後的長度)/分隔符的長度),動態組合出UNION起來的sql再執行??
動態組合出UNION起來的sql再執行
是可行方法,
又或者,算出有幾個字串,寫個迴圈,個別取出字串,判斷有無重覆,沒有重覆的,就用變數累加1。
反正,您要的這個東西,根本不需要用到 table , insert , delete 等等耗能工具。
一指搞定。
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 了.
搭配靈活運用.蠻好的.
這是抄您的啊,忘了嗎?
我當然知道啊.
對了,這二天,積極試用 postgresql
發現它真的很強大。
請教,若以此題而言,
postgresql 會怎麼寫?