樣本資料：
select date, agent, sales
from agentsales
date agent sales
2021-01-03 00:00:00.000 Agent A 10
2021-02-05 00:00:00.000 Agent A 15
2021-03-10 00:00:00.000 Agent A 10
2021-01-05 00:00:00.000 Agent B 5
2021-02-06 00:00:00.000 Agent B 28
2021-03-10 00:00:00.000 Agent B 5
2021-01-02 00:00:00.000 Agent C 35
2021-02-04 00:00:00.000 Agent C 25
2021-03-08 00:00:00.000 Agent C 15
2021-01-01 00:00:00.000 Agent D 5
2021-02-02 00:00:00.000 Agent D 35
2021-03-10 00:00:00.000 Agent D 31
我想獲得超過 30 次銷售的代理商數量，如果他們從未超過 30 次銷售，則考慮當前和前幾個月的總和，否則僅當月。
預期輸出：
YrMon Count_Agent_more_than_30_sales
Jan21 1
Feb21 2
Mar21 2
邏輯：
Jan21 - 1 since only C has crossed 30 sales
Feb21 - 2 since B and D have crossed 30 sales. Agent D has crossed the 30 mark in the month, and B has crossed over period for first time. C is not considered as it previously crossed the 30 mark.
Mar21 - 2 since A and D have crossed 30 sales. Agent A has crossed mark over period for 1st time. D has crossed for the month. B is not considered as periodic case was already considered in last month. C is not considered as it already crossed 30 mark last month.
如上所述，我想獲得超過 30 次銷售的代理商的數量，如果他們從未超過 30 次銷售，則考慮當前和前幾個月的總和，否則僅當月。
我的查詢來計算一段時間內的總和：
;WITH CTE AS (SELECT CAST(YEAR([DATE]) AS VARCHAR) ' ' CAST(MONTH([DATE]) AS VARCHAR) YRMON, AGENT, SUM(SALES) SALES
FROM AgentSales
GROUP BY CAST(YEAR([DATE]) AS VARCHAR) ' ' CAST(MONTH([DATE]) AS VARCHAR), AGENT
)
SELECT *, SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON) SUMOVERPERIOD FROM CTE
ORDER BY 2,1
YRMON AGENT SALES SUMOVERPERIOD
2021 1 Agent A 10 10
2021 2 Agent A 15 25
2021 3 Agent A 10 35
2021 1 Agent B 5 5
2021 2 Agent B 28 33
2021 3 Agent B 5 38
2021 1 Agent C 35 35
2021 2 Agent C 25 60
2021 3 Agent C 15 75
2021 1 Agent D 5 5
2021 2 Agent D 35 40
2021 3 Agent D 31 71
現在我試圖在計算出的總和上應用邏輯：
;WITH CTE AS (SELECT CAST(YEAR([DATE]) AS VARCHAR) ' ' CAST(MONTH([DATE]) AS VARCHAR) YRMON, AGENT, SUM(SALES) SALES
FROM AgentSales
GROUP BY CAST(YEAR([DATE]) AS VARCHAR) ' ' CAST(MONTH([DATE]) AS VARCHAR), AGENT
)
SELECT *, SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON) SUMOVERPERIOD,
CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 THEN 1 ELSE 0 END AS CALC
FROM CTE
ORDER BY 2,1
YRMON AGENT SALES SUMOVERPERIOD CALC
2021 1 Agent A 10 10 0
2021 2 Agent A 15 25 0
2021 3 Agent A 10 35 1
2021 1 Agent B 5 5 0
2021 2 Agent B 28 33 1
2021 3 Agent B 5 38 1
2021 1 Agent C 35 35 1
2021 2 Agent C 25 60 1
2021 3 Agent C 15 75 1
2021 1 Agent D 5 5 0
2021 2 Agent D 35 40 1
2021 3 Agent D 31 71 1
此查詢始終考慮當前和上一期間的總和。
How to check whether the sales has previously crossed the 30 sales mark and for such cases to exclude doing the sum over period? For example can we apply LAG on the result of the SUM OVER column?
uj5u.com熱心網友回復：
請檢查其中一個是否符合您的需求（我認為描述混亂）
選項1
-- If you want to count only the first time [agent] crossed 30 sales
;With MyCTE01 as (
SELECT
[date] = EOMONTH([date], -1),
[agent],[sales],
S = SUM([sales]) OVER (PARTITION BY [agent] ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
FROM [AgentSales]
),
MyCTE02 as (
SELECT [date],[agent],[sales], S
FROM MyCTE01
-- The idea of using "and S - [sales] < 30" instead of ROW_NUMBER came from @Charlieface, but it is better to do the work on DATE data type and not on string
WHERE S > 30 and S - [sales] < 30
)
SELECT DATENAME(month,[Date]), YEAR([Date]), COUNT(*)
FROM MyCTE02
GROUP BY [date]
GO
選項 2
-- If you want to count all the [agent] crossed 30 sales till now
;With MyCTE01 as (
SELECT
[date] = DATEADD(DAY, 1, EOMONTH([date], -1)),
[agent],[sales],
S = SUM([sales]) OVER (PARTITION BY [agent] ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
FROM [AgentSales]
)
,MyCTE02 as (
SELECT [date],[agent],[sales], S
FROM MyCTE01
WHERE S > 30
)
SELECT DATENAME(month,[Date]), YEAR([Date]), COUNT(*)
FROM MyCTE02
GROUP BY [date]
GO
選項 3
-- If you want to count only the first time [agent] crossed 30 sales or when the sales or over 30
;With MyCTE01 as (
SELECT
[date] = DATEADD(DAY,1,EOMONTH([date], -1)),
[agent],[sales],
S = SUM([sales]) OVER (PARTITION BY [agent] ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
FROM [AgentSales]
)
,MyCTE02 as (
SELECT [date],[agent],[sales], S
FROM MyCTE01
-- The idea of using "and S - [sales] < 30" instead of ROW_NUMBER came from @Charlieface, but it is better to do the work on DATE data type and not on string
WHERE (S > 30 and S - [sales] < 30) or sales > 30
)
SELECT DATENAME(month,[Date]), YEAR([Date]), COUNT(*)
FROM MyCTE02
GROUP BY [date]
GO
DDL DML
USE tempdb
GO
DROP TABLE IF EXISTS [AgentSales]
GO
CREATE TABLE [AgentSales](id INT IDENTITY(1,1), [date] DATE, agent VARCHAR(100), sales INT)
GO
INSERT [AgentSales]([date],[agent],[sales]) VALUES
('2021-01-03 00:00:00.000','Agent A', 10),
('2021-02-05 00:00:00.000','Agent A', 15),
('2021-03-10 00:00:00.000','Agent A',10),
('2021-01-05 00:00:00.000','Agent B',5 ),
('2021-02-06 00:00:00.000','Agent B',28),
('2021-03-10 00:00:00.000','Agent B',5 ),
('2021-01-02 00:00:00.000','Agent C',35),
('2021-02-04 00:00:00.000','Agent C',25),
('2021-03-08 00:00:00.000','Agent C',15),
('2021-01-01 00:00:00.000','Agent D',5 ),
('2021-02-02 00:00:00.000','Agent D',35),
('2021-03-10 00:00:00.000','Agent D',31)
GO
SELECT [id],[date],[agent],[sales]
FROM [AgentSales]
GO
uj5u.com熱心網友回復：
看起來這應該適合你
- 您需要預先匯總每個
agent和的銷售額month，然后獲得該匯總的運行總和 - 然后通過將當前資料與運行總和進行比較，簡單地檢查本月的每一行是否已經交叉
SELECT
YrMon = FORMAT(Month, 'yyyy MM'),
Count_Agent_more_than_30_sales =
COUNT(CASE WHEN SumOverPeriod >= 30 AND SumOverPeriod - sales < 30 OR sales >= 30 THEN 1 END)
FROM (
SELECT
Month = EOMONTH(date),
agent,
sales = SUM(sales),
SumOverPeriod = SUM(SUM(sales)) OVER (PARTITION BY agent ORDER BY EOMONTH(date)
ROWS UNBOUNDED PRECEDING)
FROM AgentSales
GROUP BY EOMONTH(date), agent
) sales
GROUP BY Month;
資料庫<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/311524.html