iT邦幫忙

2

SQL搜尋資料及語法問題,挑戰不可能的任務Part2

sql

感謝前輩在前一篇問題指點迷津,前文連結如下
前文連結
延伸前一篇的問題,目前的想要針對TypeD和E的資料集,若有溢位,則將它分配在溢位區,如下圖所示。


主要著重在要如何處理TypeD, TypeE資料集,如圖示若集料集有含跨小於min區或大於max區,則將資料集歸類在小於min區或大於max區的群組,不要歸類在中間區,也不要讓資料在兩個區域都有歸類。

不好意思,再度請教各位大大指點,感激不盡~

看更多先前的討論...收起先前的討論...
rogeryao iT邦大師 4 級 ‧ 2021-02-25 21:53:02 檢舉
這問題的邏輯怪怪的 , 若 TypeF 只有兩筆資料,他的 GROUP 是 1 (小於min) 與 3 (大於max區) ;那麼是要歸 1 還是 3 ?
leo226 iT邦新手 4 級 ‧ 2021-02-25 22:18:52 檢舉
這個在資料裡確實也是有的情況,這情況我想要姑且先把它歸在(大於max區),主要是讓他有個家住,再來解釋為什麼要這樣分,因為其實放那裡都不對。。。><
這不叫溢位. 溢位 是有其意義的,別亂用名詞.
另外我在你前一個發問中,有回答依照比例的解法了.
別沒有頭緒的亂分組. 邏輯先想好.
leo226 iT邦新手 4 級 ‧ 2021-02-26 00:04:12 檢舉
我知道它不叫溢位,只是暫時不知道該用什麼名詞來形容它,就暫時先亂用一下。。。><。。。可能叫離群值更適合XDD

1 個回答

5
rogeryao
iT邦大師 4 級 ‧ 2021-02-26 11:11:30
最佳解答

依版主描述整合如下 :
https://ithelp.ithome.com.tw/upload/images/20210226/20085021Uef3U603AY.png

CREATE TABLE TypeGroupTest (
XTypeNo int NULL , 
Xtype nvarchar(40) NULL , 
Xvalue int NULL , 
Xmin int NULL ,
Xmax int NULL); 

INSERT INTO TypeGroupTest (XTypeNo,Xtype,Xvalue,Xmin,Xmax)
VALUES 
(1,'TypeA',10,100,200),
(2,'TypeA',20,100,200),
(3,'TypeA',30,100,200),
(4,'TypeB',410,200,300),
(5,'TypeB',420,200,300),
(6,'TypeB',430,200,300),
(7,'TypeC',310,300,400),
(8,'TypeC',320,300,400),
(9,'TypeC',330,300,400),
(12,'TypeD',410,400,500),
(13,'TypeD',490,400,500),
(14,'TypeD',550,400,500),
(15,'TypeE',510,500,600),
(16,'TypeE',590,500,600),
(17,'TypeE',450,500,600),
-- 額外加入
(18,'TypeD',505,400,500),
(19,'TypeD',506,400,500),
(20,'TypeF',590,500,600),
(21,'TypeF',450,500,600),
-- 額外加入PART2
(22,'TypeG',450,600,700),
(23,'TypeG',460,600,700),
(24,'TypeG',470,600,700),
(25,'TypeG',650,600,700),
(26,'TypeG',660,600,700),
(27,'TypeG',850,600,700),
(28,'TypeG',860,600,700),
(29,'TypeG',870,600,700),
-- 額外加入PART3
(30,'TypeH',NULL,NULL,NULL);
SELECT Xtype,X1,X2,X3,
CASE 
WHEN X3 IS NOT NULL THEN 'X3'
WHEN X3 IS NULL AND X1 IS NOT NULL THEN 'X1'
WHEN X3 IS NULL AND X1 IS NULL AND X2 IS NOT NULL THEN 'X2'
WHEN X3 IS NULL AND X1 IS NULL AND X2 IS NULL THEN NULL
END AS XGROUP_NEW
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
PIVOT( SUM(TempValue) FOR XGROUP IN (X1, X2, X3)) TempP
ORDER BY Xtype

Demo

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

乾脆連樓主沒提到的情形全部考慮進來了
先按三個
/images/emoticon/emoticon12.gif/images/emoticon/emoticon12.gif/images/emoticon/emoticon12.gif

兩題我一直看下來看到現在
感覺起來
好像 Type 才是真正的 Group
/images/emoticon/emoticon06.gif

leo226 iT邦新手 4 級 ‧ 2021-02-26 14:43:05 檢舉

感謝前輩指教,目前單位使用的cloudera好像不支援PIVOT語法
所以目前先用UNION的方式來達到我的目的
Demo

SELECT [Xtype],[XGROUP]
FROM (
SELECT [Xtype],[XGROUP],
COUNT(1) OVER(PARTITION BY [Xtype]) AS ROWNUM
FROM (
SELECT DISTINCT [Xtype],
CASE
  WHEN [Xvalue] <= [Xmin]
  THEN 'X1'
  WHEN [Xvalue] >= [Xmax]
  THEN 'X3'
  WHEN [Xvalue] BETWEEN [Xmin] AND [Xmax]
  THEN 'X2'
END AS [XGROUP]
FROM [TypeGroupTest]) AS TempA
) AS TempB
WHERE ROWNUM=1
UNION 
SELECT [Xtype],[XGROUP]
FROM (
SELECT [Xtype],[XGROUP],
COUNT(1) OVER(PARTITION BY [Xtype]) AS ROWNUM
FROM (
SELECT DISTINCT [Xtype],
CASE
  WHEN [Xvalue] <= [Xmin]
  THEN 'X1'
  WHEN [Xvalue] >= [Xmax]
  THEN 'X3'
  ELSE 'X4'
END AS [XGROUP]
FROM [TypeGroupTest]) AS TempA
) AS TempB
WHERE ROWNUM=2 AND XGROUP <> 'X4'
UNION 
SELECT [Xtype],[XGROUP]
FROM (
SELECT [Xtype],[XGROUP],
COUNT(1) OVER(PARTITION BY [Xtype]) AS ROWNUM
FROM (
SELECT DISTINCT [Xtype],
CASE
  WHEN [Xvalue] <= [Xmin]
  THEN 'X1'
  WHEN [Xvalue] >= [Xmax]
  THEN 'X3'
  ELSE 'X2'
  END AS [XGROUP]
FROM [TypeGroupTest]) AS TempA
) AS TempB
WHERE ROWNUM=3 AND XGROUP NOT IN ('X1', 'X2')

這樣感覺很瞎...><

海綿寶寶 也可以這樣說,Type 是一堆還沒有被歸類的資料,剛好同一個type都視為同一組資料,處理資料時再透過SQL針對條件來重新歸類分群

rogeryao iT邦大師 4 級 ‧ 2021-02-26 15:02:15 檢舉

cloudera好像不支援PIVOT語法,那就換個語法.

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

leo226 iT邦新手 4 級 ‧ 2021-02-26 15:36:37 檢舉

這語法真是太強了,獲益良多,超級感謝~

我要發表回答

立即登入回答