iT邦幫忙

1

sql case when 多判斷 想請高手指導小弟

p_0215 2018-12-10 13:20:0014966 瀏覽
select order
,COUNT(DISTINCT(CASE WHEN af !='0' AND ag IN ('123','124','125','126') THEN sn END)) AS BB
,COUNT(DISTINCT(CASE WHEN (ag='126' and af='0') AND sn  NOT IN  (CASE WHEN af !='0' AND ag IN ('123','124','125','126') THEN sn  ELSE '' END)THEN sn END)) AS FF
  from Table_1
  group by order

我有個table

order   sn       ag      af
a       0001     123     0
a       0001     124     0
a       0001     125     0
a       0001     126     0
a       0003     123     1
a       0003     123     2
a       0003     124     0
a       0003     125     0
a       0003     126     0
b       0005     123     0
b       0005     124     0
b       0006     123     0
b       0006     124     1
b       0006     124     2
b       0006     125     0

想產出的結果

order         bb        ff
a             1         1
b             1         0

bb這欄位是計算sn中若有一筆資料在af欄位中出現過不為0就計數
ff這欄位是計算sn中ag='126' and af = '0',且這個sn在其他筆資料ag in('123','124','125')中af皆為 '0'

如上述例子中
0001這個sn,4筆資料af皆為'0',故ff欄位中計數'1'
0003這個sn雖然ag= '126' and as = '0',但他多筆資料中曾經出現過!= '0',故計數在BB欄位中
0005這個sn 無ag= '126' and as = '0',不計數
0006這個sn,他多筆資料中曾經出現過!= '0',故計數在BB欄位中

請各位高手大大指導,感激不盡

黃彥儒 iT邦高手 1 級 ‧ 2018-12-10 13:48:06 檢舉
看的出來你有打算排版,可以使用Markdown的功能排會比較漂亮
https://ithelp.ithome.com.tw/markdown#mk_table
rogeryao iT邦大師 1 級 ‧ 2018-12-11 00:21:43 檢舉
突然發現一個有趣的問題 :
若原資料多插入一筆
b 0005 126 0
那產出的結果 b 的 ff 值會是甚麼 ?
我猜,應該是 1 吧
ff 條件:
1. ag='126' and af = '0'
2. ag in('123','124','125')中 af 皆為 '0'
這兩點都符合了
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
6
張小馬~
iT邦新手 3 級 ‧ 2018-12-10 14:25:57
最佳解答

這應該是在考邏輯不是考語法齁~~ XDD
https://ithelp.ithome.com.tw/upload/images/20181210/20111566smyaYBijbM.png

補充原po的描述:

0001這個sn,【第一條件:有出現過126,而且126對應的af都是0。】(換句話說,126不旦有出現,而且並沒有發生【出現了126且對應的af不是0】的狀況),再加上【第二條件:其他非126的全部都是0。】(並沒有發生其中一個不是0的狀況),因此ff會計入0001這個sn;0001這個sn,並沒有發生bb的條件【af至少一個不是0】,所以沒有計入bb。

0003這個sn,雖然滿足第一條件:ag= '126' and af = '0',但不滿足第二條件,它多筆資料中曾經出現過!= '0',故不計入ff;另外,因為滿足bb條件【af至少一個不是0】故計數在bb欄位中。

0005這個sn,無ag= '126' and af = '0',不滿足第一條件,故不計入ff;因沒滿足bb條件【af至少一個不是0】故不計在bb欄位中。

0006這個sn,無ag= '126' and af = '0',不滿足第一條件,故不計入ff;因為滿足bb條件【af至少一個不是0】故計數在bb欄位中。

請問是否同意廢除【終止運轉的條文】?不同意的話是要繼續運轉還是不要繼續運轉?

完整語法如下:

select D."order",SUM(bb_tag) bb,SUM(ff_tag) ff
from(
select C."order",C.sn,C.bb_tag,C.ff_tag
from(
select B.*,
CASE WHEN (tag3+tag4+tag5+tag6)>0               THEN 1 ELSE 0 END bb_tag,
CASE WHEN (tag3+tag4+tag5+tag6)=0 and tag6_2>=1 THEN 1 ELSE 0 END ff_tag
from(
select A.*,
SUM(tag123)   over (PARTITION BY "order",sn) tag3,
SUM(tag124)   over (PARTITION BY "order",sn) tag4,
SUM(tag125)   over (PARTITION BY "order",sn) tag5,
SUM(tag126)   over (PARTITION BY "order",sn) tag6,
SUM(tag126_2) over (PARTITION BY "order",sn) tag6_2

from(
SELECT *,
CASE WHEN ag = '123' and af != '0' THEN 1 ELSE 0 END tag123,
CASE WHEN ag = '124' and af != '0' THEN 1 ELSE 0 END tag124,
CASE WHEN ag = '125' and af != '0' THEN 1 ELSE 0 END tag125,
CASE WHEN ag = '126' and af != '0' THEN 1 ELSE 0 END tag126,
CASE WHEN ag = '126' and af  = '0' THEN 1 ELSE 0 END tag126_2
FROM "ithelp_20181210_1"
) A
) B
) C
group by C."order",C.sn,C.ff_tag,C.bb_tag
) D
group by D."order" order by D."order"
--2018/12/10 PM20:39 精簡過

講幾個關鍵就好:

  1. 要知道被ff和bb計算的【必要條件】是什麼?什麼狀態下會破功不被計算?像ff有個必要條件是一定要出現126而且為0,沒出現126就不會被計算,這是tag126_2的設計原因。
  2. ag縱使在相同的order和sn之下,仍不是唯一值,這是必須做B層的原因,先將相同ag有沒有出現破功的狀況給判斷出來。
  3. 針對order和sn做over partition,讓tag在相同的order和sn有唯一值,取得唯一值之後,就可以直接對order,sn,tag做group by(D層做的事),最後再計算個數(最外層)。
p_0215 iT邦新手 5 級 ‧ 2018-12-11 09:43:37 檢舉

感謝大大熱心指導,小弟我由衷的感謝與學習

3
rogeryao
iT邦大師 1 級 ‧ 2018-12-10 21:58:09

1.假設 af 最小值 ='0'
2.測試資料多加一筆 ('b','0005','126','0')
3.MS SQL :

Type A:
-- [ag_123],[ag_124],[ag_125],[ag_126] 4 個皆為 '0' , ff=1

SELECT [order],
SUM(CASE WHEN [ag_123]<>'0' OR [ag_124]<>'0'
OR [ag_125]<>'0' OR [ag_126]<>'0' THEN 1 ELSE 0 END) AS bb,
SUM(CASE WHEN [ag_123]+[ag_124]+[ag_125]+[ag_126]='0000' THEN 1 ELSE 0 END) AS ff
FROM (
--行轉列
SELECT [order],[sn],[123] AS 'ag_123',[124] AS 'ag_124',
[125] AS 'ag_125',[126] AS 'ag_126'
FROM  (SELECT [order],[sn],[ag],[af] FROM  XTable) AS MTable
PIVOT (MAX([af]) FOR [ag] IN ([123],[124],[125],[126])) PTable
--
) AS YTable
WHERE 1=1
GROUP BY [order]
ORDER BY [order]

Type B:
-- [ag_126]='0' 且 其它 3 個為 '0' 或 null , ff=1

SELECT [order],
SUM(CASE WHEN [ag_123]<>'0' OR [ag_124]<>'0'
OR [ag_125]<>'0' OR [ag_126]<>'0' THEN 1 ELSE 0 END) AS bb,
SUM(CASE WHEN (([ag_123]='0' or [ag_123] is null) 
and ([ag_124]='0' or [ag_124] is null)
and ([ag_125]='0' or [ag_125] is null)) and [ag_126]='0' THEN 1 ELSE 0 END) AS ff
FROM (
--行轉列
SELECT [order],[sn],[123] AS 'ag_123',[124] AS 'ag_124',
[125] AS 'ag_125',[126] AS 'ag_126'
FROM  (SELECT [order],[sn],[ag],[af] FROM  XTable) AS MTable
PIVOT (MAX([af]) FOR [ag] IN ([123],[124],[125],[126])) PTable
--
) AS YTable
WHERE 1=1
GROUP BY [order]
ORDER BY [order]

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8d811467bda186f735efd63da6b834cf

p_0215 iT邦新手 5 級 ‧ 2018-12-11 10:01:21 檢舉

感謝大大細心的指導,這2種type的做法,我會TRY看看
謝謝

3
小碼農米爾
iT邦高手 1 級 ‧ 2018-12-10 22:10:12

不知道大大用哪一種SQL,小弟使用 MsSql。

SQL語法

;WITH A AS
(
	SELECT [order], sn,
		   SUM(CASE WHEN af<>'0' THEN 1 ELSE 0 END) AS rule1,
		   SUM(CASE WHEN ag='126' AND af='0' THEN 1 ELSE 0 END) AS rule2,
		   SUM(CASE WHEN ag IN('123','124','125') AND af<>'0' THEN 1 ELSE 0 END) AS rule3
	FROM @Data
	GROUP BY [order], sn
)
--SELECT * FROM A
,B AS
(
	SELECT [order], sn,
		   CASE WHEN rule1>0 THEN 1 ELSE 0 END AS bb,
		   CASE WHEN rule2>=1 AND rule3=0 THEN 1 ELSE 0 END AS ff
	FROM A
)
--SELECT * FROM B
,C AS
(
	SELECT [order],
		   SUM(bb) AS bb,
		   SUM(ff) AS ff
	FROM B
	GROUP BY [order]
)

SELECT * FROM C ORDER BY [order]

資料SQL

DECLARE @Data TABLE
(
	[order] NVARCHAR(10),
	sn NVARCHAR(10),
	ag NVARCHAR(10),
	af NVARCHAR(10)
)

INSERT INTO @Data 
([order], sn, ag, af) 
	VALUES
('a','0001','123','0'),
('a','0001','124','0'),
('a','0001','125','0'),
('a','0001','126','0'),
('a','0003','123','1'),
('a','0003','123','2'),
('a','0003','124','0'),
('a','0003','125','0'),
('a','0003','126','0'),
('b','0005','123','0'),
('b','0005','124','0'),
('b','0006','123','0'),
('b','0006','124','1'),
('b','0006','124','2'),
('b','0006','125','0')

說明

第一段
先以 order + sn 將資料分組,並計算下面三個規則
這三個規則是我歸納出可以在第一階段計算出來的部分

  1. rule1: 計算 af<>'0' 的數量
  2. rule2: 計算 ag='126' AND af='0' 的數量
  3. rule3: 計算 ag IN('123','124','125') AND af<>'0' 的數量,這個部分使用反向邏輯
order   sn   rule1 rule2 rule3
-----|------|-----|-----|------
  a	 | 0001	|  0  |  1  |  0
  a	 | 0003	|  2  |  1  |  2
  b	 | 0005	|  0  |  0  |  0
  b	 | 0006	|  2  |  0  |  2

第二段
到第二階段就可以計算出每個 sn 的 bb 和 ff

  1. bb: 判斷 rule1>0
  2. ff: 判斷 rule2>=1 且 rule3=0
order   sn    bb    ff
-----|------|-----|-----
  a	   0001	   0	 1
  a	   0003	   1	 0
  b	   0005	   0	 0
  b	   0006	   1	 0

第三段
再以 order 分組一次並加總 bb 和 ff,就是期望的結果

order   bb    ff
-----|-----|-----
  a     1	  1
  b     1	  0

測試SQL


MySql 版本,不使用 CTE

SELECT `order`,
        SUM(bb) AS bb,
        SUM(ff) AS ff
FROM 
(
	SELECT `order`, sn,
 	        CASE WHEN rule1>0 THEN 1 ELSE 0 END AS bb,
 	        CASE WHEN rule2>=1 AND rule3=0 THEN 1 ELSE 0 END AS ff
	FROM 
	(
		SELECT `order`, sn,
		        SUM(CASE WHEN af<>'0' THEN 1 ELSE 0 END) AS rule1,
		        SUM(CASE WHEN ag='126' AND af='0' THEN 1 ELSE 0 END) AS rule2,
		        SUM(CASE WHEN ag IN('123','124','125') AND af<>'0' THEN 1 ELSE 0 END) AS rule3
		FROM Data
		GROUP BY `order`, sn
	) AS A
) AS B
GROUP BY `order`
ORDER BY `order`

測試SQL

我要發表回答

立即登入回答