iT邦幫忙

0

SQL 一次count多個條件

各位高手大大好 小弟剛開始自學SQL
找了一些自己有興趣的資料來做學習
以下資料是台股指數的資料
https://ithelp.ithome.com.tw/upload/images/20220815/20151453ZAaiB7sfWp.png
我想要將每天的漲跌(move)分段做日數的計算
我是分為-200up,-100~-200,-50~-100,0~-50,0~50,50~100,100~200,200up
目前我可以單獨做count去設定條件計算
但想要一次跑完 想請問該怎麼做 謝謝大大
如果可以希望能幫我解答詳細一點 我想了好久也改好多次QQ 可能我沒有學得很透
萬分感謝

obarisk iT邦研究生 2 級 ‧ 2022-08-15 23:23:58 檢舉
sum(case when end) as xxx
obarisk iT邦研究生 2 級 ‧ 2022-08-15 23:24:20 檢舉
或是 countif
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
2
海綿寶寶
iT邦大神 1 級 ‧ 2022-08-16 08:32:05

參考這篇

5
水無痕
iT邦新手 3 級 ‧ 2022-08-16 09:05:29

突發奇想,把條件定義在 mapping table 中 !
環境:SQL Server 2019

DECLARE @data TABLE
              (
                  [date]   DATETIME,
                  [point]  DECIMAL(18, 2),
                  [move]   DECIMAL(18, 2),
                  [change] DECIMAL(18, 2)
              )
INSERT INTO @data([date], [point], [move], [change])
VALUES ('2022-08-12', 15288.97, 91.12, 0.60),
       ('2022-08-11', 15197.85, 258.83, 1.73),
       ('2022-08-10', 14939.02, -111.26, -0.74),
       ('2022-08-09', 15050.28, 29.87, 0.20),
       ('2022-08-08', 15020.41, -15.63, -0.10),
       ('2022-08-05', 15036.04, 333.84, 2.27),
       ('2022-08-04', 14702.2, -74.82, -0.51);

DECLARE @groupByCondition TABLE
                          (
                              [description] NVARCHAR(200),
                              [upper]       INT,
                              [lower]       INT
                          );

INSERT INTO @groupByCondition([description], [upper], [lower])
VALUES (N'低於 -200', -200, NULL),
       (N'介於 -200 至 -100', -100, -200),
       (N'介於 -100 至 -50', -50, -100),
       (N'介於 -50 至 0', 0, -50),
       (N'介於 0 至 50', 50, 0),
       (N'介於 50 至 100', 100, 50),
       (N'介於 100 至 200', 200, 100),
       (N'高於 200', NULL, 200);

SELECT [g].[description],
       COUNT([g].[description]) AS [count]
FROM @data [d]
    JOIN @groupByCondition [g]
         ON ([g].[lower] IS NULL OR [d].[move] >= [g].[lower])
             AND ([g].[upper] IS NULL OR [d].[move] < [g].[upper])
GROUP BY [g].[description]

執行結果
https://ithelp.ithome.com.tw/upload/images/20220816/20137005wYg6StFiCA.png

1

先利用一下暫時欄位建立完成後。再依暫存欄位來做GROUP處理
例子

SELECT keyGroup,count(*) AS num FROM (
    SELECT *,CASE 
    WHEN move <= 0 THEN '小於0'
    WHEN move > 0 && move <= 100 THEN '1~100'
    WHEN move > 100 && move <= 200 THEN '101~200'
    .....(依此類推)
    END AS keyGroup
    FORM db
) AS testDb GROUP BY keyGroup

用這樣的方式,再去搭配CASE來去生成要做群組化的值後。
再依其值做群組化計算就行了

1
wayne0127
iT邦新手 5 級 ‧ 2022-10-01 00:36:38
SELECT
(SELECT COUNT(`move`) FROM `[table_name]` WHERE `move` <= -200) AS '-200up',
(SELECT COUNT(`move`) FROM `[table_name]` WHERE `move` BETWEEN -199 AND -100) AS '-100~-200',
(SELECT COUNT(`move`) FROM `[table_name]` WHERE `move` BETWEEN -99 AND -50) AS '-50~-100',
(SELECT COUNT(`move`) FROM `[table_name]` WHERE `move` BETWEEN -49 AND 0) AS '0~-50',
(SELECT COUNT(`move`) FROM `[table_name]` WHERE `move` BETWEEN 1 AND 50) AS '0~50',
(SELECT COUNT(`move`) FROM `[table_name]` WHERE `move` BETWEEN 51 AND 100) AS '50~100',
(SELECT COUNT(`move`) FROM `[table_name]` WHERE `move` BETWEEN 101 AND 200) AS '100~200',
(SELECT COUNT(`move`) FROM `[table_name]` WHERE `move` > 200) AS '200up'
1
mudkip0406
iT邦新手 5 級 ‧ 2022-10-03 17:10:40

請試試看這樣

select count(CASE WHEN move BETWEEN -10000 AND -200 THEN 1 END) as count_neg_201_10000,
     count(CASE WHEN move BETWEEN -200 AND -101 THEN 1 END) as count_neg_101_200,
     count(CASE WHEN move BETWEEN -100 AND -51 THEN 1 END) as count_neg_51_100,
     count(CASE WHEN move BETWEEN -50 AND 0 THEN 1 END) as count_neg_0_50,
     count(CASE WHEN move BETWEEN 0 AND 50 THEN 1 END) as count0_50,
     count(CASE WHEN move BETWEEN 51 AND 100 THEN 1 END) as count51_100,
     count(CASE WHEN move BETWEEN 101 AND 200 THEN 1 END) as count101_200,
     count(CASE WHEN move BETWEEN 201 AND 10000 THEN 1 END) as count201_10000
from
    stock;

我要發表回答

立即登入回答