各位高手大大好 小弟剛開始自學SQL
找了一些自己有興趣的資料來做學習
以下資料是台股指數的資料
我想要將每天的漲跌(move)分段做日數的計算
我是分為-200up,-100~-200,-50~-100,0~-50,0~50,50~100,100~200,200up
目前我可以單獨做count去設定條件計算
但想要一次跑完 想請問該怎麼做 謝謝大大
如果可以希望能幫我解答詳細一點 我想了好久也改好多次ＱＱ 可能我沒有學得很透
萬分感謝
已邀請的邦友 {{ invite_list.length }}/5
突發奇想，把條件定義在 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]
執行結果
先利用一下暫時欄位建立完成後。再依暫存欄位來做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來去生成要做群組化的值後。
再依其值做群組化計算就行了
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'
請試試看這樣
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;
iThome鐵人賽
看影片追技術