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

sql

leo226 2021-02-25 02:13:44 ‧ 3011 瀏覽

分享至

各位先進好，問題有點複雜，希望用圖表有說明的更清楚～
需求說明：
　　有一堆資料集合（資料結構範例如圖一，有Type(A,B,C,D,E)），想要依條件分群去除不合條件的資料後，再篩選出不重複的資料show在DataTable上。
依範例資料結構，最終需篩選出Type(A,B,C)於DataTable上

原始資料結構如下圖所示

篩選條件情境如下圖所示，這邊將每組資料集的min及max條件都假定為一樣的示意圖來說明

篩選條件說明：
　　每組資料都有value, min, max的值，想要將所有資料都小於min，或所有資料都大於max，或所有資料都介於中間的資料篩選出來，若資料有橫跨兩個條件的資料集合則要排除掉，資料需全部都座落在同一個條件區域。

篩選出來的資料需要有type資訊及分群編號的資訊，這樣才能知道每一組資料集合是屬於那一群。

如果不分開處理，只能用一個SQL來篩選這些資料，不知道是否有可能做到。

個人的淺見好像是個不可能的任務。。。＞＜

還請各位先進指點，謝謝

japhenchen iT邦超人 1 級 ‧ 2021-02-25 08:28:33 檢舉

有沒有規定一定要哪個牌子的SQL？MySQL? MSSQL? ORACLE ? mariaDB ? postgreSQL? noSQL ? SQLite ?

leo226 iT邦新手 4 級 ‧ 2021-02-25 11:20:22 檢舉

是用cloudera db~

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

rogeryao

iT邦超人 7 級 ‧ 2021-02-25 10:21:57

最佳解答

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);

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] >= [Xmin] AND [Xvalue] <= [Xmax]
  THEN 'X2'
  ELSE 'X4'
END AS [XGROUP]
FROM [TypeGroupTest]) AS TempA
) AS TempB
WHERE ROWNUM=1

Demo

回應 10
分享
檢舉

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

海綿寶寶 iT邦大神 1 級 ‧ 2021-02-25 10:27:48 檢舉

這麼無厘頭的需求都寫得出來
我先按三個

leo226 iT邦新手 4 級 ‧ 2021-02-25 11:45:20 檢舉

太利害了，感謝指教，太強了～第一次學到這語法～
想要進階請教問題，若不將資料排除，而想要將TypeD和E計算資料分佈比例來分群的話，有方法可以處理嗎？
例如：TypeD的大多數資料都座落在>max的範圍，所以將TypeD歸納為X3群組
TypeE的大多數資料都座落在> max AND < min 的範圍，所以將TypeE歸納為X2群組

rogeryao iT邦超人 7 級 ‧ 2021-02-25 11:54:16 檢舉

進階的問題不易明瞭,請另外提問並詳細說明.
建議:
1.先在 https://dbfiddle.uk/?rdbms=sqlserver_2017 建好模擬的資料
2.貼上預期的結果

leo226 iT邦新手 4 級 ‧ 2021-02-25 17:19:54 檢舉

rogeryao測試有成功了，但不知怎麼的，在實際線上實作，我的ROWNUM都是從2開始計數，肉眼看明明PARTITION 完後只有一筆資料，為什麼都會計算出為2，不知道有藏什麼鬼。
接下來要處理進一步的問題，我再整理資料說明，謝謝～

rogeryao iT邦超人 7 級 ‧ 2021-02-25 17:22:17 檢舉

原問題的邏輯有個疑點(我無法確定你的定義,所以沒改)
WHEN 內的 =

WHEN [Xvalue] >= [Xmin] AND [Xvalue] <= [Xmax]
可能要改成
WHEN [Xvalue] > [Xmin] AND [Xvalue] < [Xmax]
否則
[Xvalue] = [Xmin] 時是 X1 還是 X2
[Xvalue] = [Xmax] 時是 X3 還是 X2

leo226 iT邦新手 4 級 ‧ 2021-02-25 20:29:15 檢舉

嗯，這我有發現，原則上應該是去掉等號沒錯。
但我PARTITION出來真的都是從2開始計算，真的很神奇，一開始用WHERE ROWNUM=1一直找不到資料還以為自己那裡寫錯了，除錯了好久，攤開row data的ROWNUM才發現竟然都是2

rogeryao iT邦超人 7 級 ‧ 2021-02-26 08:54:37 檢舉

進階的問題 :
1.依佔比方式分類
2.假設相同 Type 的 Group 佔比大於等於 67% 才視為有效

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);

SELECT Xtype,XGROUP,PERCENTAGE
FROM (
SELECT Xtype,XGROUP,
ROUND(1.00*ROWNUM2/ROWNUM3,2)*100 AS PERCENTAGE,
DENSE_RANK() OVER(PARTITION BY Xtype ORDER BY Xtype,ROWNUM2 DESC) AS ROWID
FROM (
--# SELECT Xtype,XGROUP,ROWNUM2
--# FROM (
SELECT Xtype,XGROUP,
COUNT(1) OVER(PARTITION BY Xtype) AS ROWNUM1,
COUNT(1) AS ROWNUM2,
SUM(COUNT(1)) OVER(PARTITION BY Xtype) AS ROWNUM3
FROM (
SELECT Xtype,
CASE
  WHEN Xvalue < Xmin
  THEN 'X1'
  WHEN Xvalue > Xmax
  THEN 'X3'
  WHEN Xvalue >= Xmin AND Xvalue <= Xmax
  THEN 'X2'
  ELSE 'X4'
END AS XGROUP
FROM TypeGroupTest) AS TempA
GROUP BY Xtype,XGROUP
--# ) AS TempB
--# WHERE ROWNUM1>1
) AS TempC
) AS TempD
WHERE ROWID=1
AND PERCENTAGE>=67

Demo

leo226 iT邦新手 4 級 ‧ 2021-02-26 09:35:07 檢舉

非常感謝前輩指點，發現個問題，範例中因為TypeF同時被分到X1, X2兩個群裡，且同為50%的佔比，若where條件下>=50%，最後會被同時抓取出來，且同一個Type資料會同時被分到兩個群裡面～

rogeryao iT邦超人 7 級 ‧ 2021-02-26 09:52:58 檢舉

"發現個問題"...
那個資料是我故意做的,在50%的佔比會被分到兩個群裡是"反映出事實",
你也可以修改
DENSE_RANK() OVER(PARTITION BY Xtype ORDER BY Xtype,ROWNUM2 DESC) AS ROWID

=>改用 ROW_NUMBER(), ORDER BY 也要改 ;
就只會出現 X1 或 X2

leo226 iT邦新手 4 級 ‧ 2021-02-26 11:12:02 檢舉

了解，謝謝～

登入發表回應

迷路

iT邦新手 1 級 ‧ 2021-02-25 08:32:58

先不要急著做DISTINCT，先取得每筆資料的GROUP
然後用type+GROUP做條件GROUP BY，再用type去GROUP BY取得COUNT()的數量
限制數量為一的資料
SELECT b.type,b.GROUP,COUNT(*) AS Num FROM (SELECT a.type,a.GROUP FROM(SELECT type,CASE .... END AS GROUP FROM TypeGroupTest) a GROUP BY a.type,a.GROUP) b GROUP BY b.type HAVING Num=1

回應 3
分享
檢舉

leo226 iT邦新手 4 級 ‧ 2021-02-25 17:22:12 檢舉

謝謝指導～

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

最後一段的HAVING套用上去，顯示
Invalid column name 'Nums'.
不知道是什麼原因無法使用HAVING

SELECT b.Xtype,b.XGROUP,COUNT(*) AS Nums FROM (
SELECT a.Xtype,a.XGROUP FROM(
SELECT Xtype,
CASE
  WHEN [Xvalue] <= [Xmin]
  THEN 'X1'
  WHEN [Xvalue] >= [Xmax]
  THEN 'X3'
  WHEN [Xvalue] >= [Xmin] AND [Xvalue] <= [Xmax]
  THEN 'X2'
  ELSE 'X4'
END AS [XGROUP] FROM TypeGroupTest) a 
GROUP BY a.Xtype,a.XGROUP) b 
GROUP BY b.Xtype,b.XGROUP
HAVING b.Nums = 1

迷路 iT邦新手 1 級 ‧ 2021-07-30 08:12:46 檢舉

因為Nums這個名稱來源自

COUNT(*) AS Nums

所以要把b.拿掉

登入發表回應

一級屠豬士

iT邦大師 1 級 ‧ 2021-02-25 21:16:16

create table t0225 (
  id int not null
, ytype text not null
, yvalue int not null
, ymin int not null
, ymax int not null); 

insert into t0225 
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);

with t1 as (
select id
     , ytype
     , case 
         when yvalue < ymin
          then 'G1'
         when yvalue > ymax
          then 'G3'
         when yvalue >= ymin and yvalue <= ymax
          then 'G2'
         else 'G4'
       end grp
  from t0225
), t2 as (
select ytype
     ,count(*) as typecnt
  from t0225
 group by ytype
), t3 as (
select t2.ytype
     , round(count(*) filter(where grp = 'G1')::numeric / t2.typecnt,2) as percg1
     , round(count(*) filter(where grp = 'G2')::numeric / t2.typecnt,2) as percg2
     , round(count(*) filter(where grp = 'G3')::numeric / t2.typecnt,2) as percg3
  from t2
  join t1
  using (ytype)
  group by t2.ytype, t2.typecnt
), t4 as (
select ytype
     , greatest(percg1, percg2, percg3) gst
  from t3
)
select t3.ytype
     , concat(case when percg1 = gst then 'G1' end
            , case when percg2 = gst then 'G2' end
            , case when percg3 = gst then 'G3' end)
  from t3
  join t4
  using(ytype)
  order by 1
;

Demo

按照佔比方式分類.