0

## SQL搜尋資料及語法問題，挑戰不可能的任務Part3

``````SELECT Xtype,X1,X2,X3,
CASE
WHEN X3 <> 0 THEN 'X3'
WHEN X3 = 0 AND X1 <> 0 THEN 'X1'
WHEN X3 = 0 AND X1 = 0 AND X2 <> 0 THEN 'X2'
WHEN X3 = 0 AND X1 = 0 AND X2 = 0 THEN NULL
END AS XGROUP_NEW
FROM (
SELECT Xtype,
SUM(CASE WHEN XGROUP='X1' THEN TempValue ELSE 0 END) AS 'X1',
SUM(CASE WHEN XGROUP='X2' THEN TempValue ELSE 0 END) AS 'X2',
SUM(CASE WHEN XGROUP='X3' THEN TempValue ELSE 0 END) AS 'X3'
FROM (
SELECT Xtype,1 AS TempValue,
CASE
WHEN Xvalue <= Xmin
THEN 'X1'
WHEN Xvalue >= Xmax
THEN 'X3'
WHEN Xvalue > Xmin AND Xvalue < Xmax
THEN 'X2'
END AS XGROUP
FROM TypeGroupTest) AS TempA
GROUP BY Xtype
) AS TempB
ORDER BY Xtype
``````

Demo

G1：5%<=min OR 95%>=max
G2：5%~25%<=min OR 75%~95%>=max
G4：min > 0%~100% < max
G3：若資料集沒有設定min或max(min=0 OR max=0)則分類為G3

1.若班級裡學生的分數全部都落在min~max之間，則將它分類為正常班（G4）
2.若班級裡前5%學生裡任何一位學生的分數小於min，則將它分類為加強班（G1）
3.若班級裡在5%~25%的學生裡任何一位學生的分數小於min，則將它分類為觀察班（G2）
4.若班級裡沒有設定（min=0 or max=0）條件的，則將它分類為等待定義班（G3）

rogeryao iT邦大師 1 級 ‧ 2021-03-22 09:27:12 檢舉

leo226 iT邦新手 4 級 ‧ 2021-03-22 10:25:04 檢舉

25%就是第25筆
75%就是第75筆
Group2資料量共1000筆
25%就是第250筆
75%就是第750筆

G1資料集總數 X 5% ~ G1資料集總數 X 25% = 5%~25%區間資料集

1,2,3,4,.....98,99,100

leo226 iT邦新手 4 級 ‧ 2021-03-22 12:32:49 檢舉

### 2 個回答

0
rogeryao
iT邦大師 1 級 ‧ 2021-03-23 13:22:40

``````SELECT Xtype,G1,G2,G3,G4,
CASE WHEN G3 <> 0 THEN 'G3'
WHEN G3 = 0 AND G2 <> 0 THEN 'G2'
WHEN G3 = 0 AND G2 = 0 AND G1 <> 0 THEN 'G1'
WHEN G3 = 0 AND G1 = 0 AND G2 = 0 AND G4 <> 0 THEN 'G4'
END AS XGROUP_NEW
FROM (
SELECT Xtype,
SUM(CASE WHEN Xmin=0 OR Xmax=0 THEN 1 ELSE 0 END) AS 'G3',
SUM(CASE WHEN XvalueTypeMin>Xmin AND XvalueTypeMax<Xmax THEN 1 ELSE 0 END) AS 'G4',
SUM((CASE WHEN Group1='S1' AND Xvalue <=Xmin THEN 1 ELSE 0 END) + (CASE WHEN Group1='S5' AND Xvalue >=Xmax THEN 1 ELSE 0 END)) AS 'G1',
SUM((CASE WHEN Group1='S2' AND Xvalue <=Xmin THEN 1 ELSE 0 END) + (CASE WHEN Group1='S4' AND Xvalue >=Xmax THEN 1 ELSE 0 END)) AS 'G2'
FROM (
SELECT *,
CASE WHEN (Percentage>0 AND Percentage<=5) THEN 'S1'
WHEN (Percentage>5 AND Percentage<=25) THEN 'S2'
WHEN (Percentage>25 AND Percentage<=75) THEN 'S3'
WHEN (Percentage>75 AND Percentage<=95) THEN 'S4'
WHEN (Percentage>95 AND Percentage<=100) THEN 'S5' END AS Group1,
MIN(Xvalue) OVER(PARTITION BY Xtype) AS XvalueTypeMin,
MAX(Xvalue) OVER(PARTITION BY Xtype) AS XvalueTypeMax
FROM (
SELECT Xtype,Xvalue,ISNULL(Xmin,0) AS Xmin,ISNULL(Xmax,0) AS Xmax,ROW_NUMBER() OVER(PARTITION BY Xtype ORDER BY Xvalue) AS RowNum,
100.00*ROW_NUMBER() OVER(PARTITION BY Xtype ORDER BY Xvalue)/COUNT(1) OVER(PARTITION BY Xtype) AS Percentage
FROM TypeGroupTest
) AS TEMPA
) AS TEMPB
GROUP BY Xtype
) AS TEMPC
ORDER BY Xtype
``````

Demo

leo226 iT邦新手 4 級 ‧ 2021-04-03 00:13:08 檢舉

0
pojen
iT邦新手 1 級 ‧ 2021-03-24 11:25:43

(注意除了一開始生成的測試數據, 下面三張圖片都是同一個 SQL. 只是我展示不同的片段)

leo226 iT邦新手 4 級 ‧ 2021-04-03 00:12:40 檢舉