sql-server - 如何根据条件重置累计和？
问题描述
样本数据：
CREATE TABLE [dbo].[agent_sales]
(
[date] [date] NULL,
[agent] [nvarchar](50) NULL,
[sale] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-03' AS Date), N'Agent A', 10)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-05' AS Date), N'Agent A', 5)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent A', 20)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent A', 2)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-05' AS Date), N'Agent B', 5)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-06' AS Date), N'Agent B', 28)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent B', 5)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent B', 10)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-02' AS Date), N'Agent C', 35)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-04' AS Date), N'Agent C', 25)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-08' AS Date), N'Agent C', 15)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent C', 10)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-01' AS Date), N'Agent D', 5)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-02' AS Date), N'Agent D', 35)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent D', 31)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent D', 10)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-01' AS Date), N'Agent E', 32)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-01' AS Date), N'Agent E', 0)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent E', 20)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent E', 12)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-01' AS Date), N'Agent F', 32)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-02' AS Date), N'Agent F', 9)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent F', 11)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent F', 12)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-01' AS Date), N'Agent G', 32)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-02' AS Date), N'Agent G', 0)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent G', 20)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent G', 8)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-01' AS Date), N'Agent H', 32)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent H', 20)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent H', 8)
SELECT 语句输出：
select date, agent, sale
from agent_sales
date agent sales
--------------------------------------
2021-01-03 00:00:00.000 Agent A 10
2021-02-05 00:00:00.000 Agent A 5
2021-03-10 00:00:00.000 Agent A 20
2021-04-10 00:00:00.000 Agent A 2
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-04-10 00:00:00.000 Agent B 10
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-04-10 00:00:00.000 Agent C 10
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
2021-04-10 00:00:00.000 Agent D 10
2021-01-01 00:00:00.000 Agent E 32
2021-02-02 00:00:00.000 Agent E 0
2021-03-10 00:00:00.000 Agent E 20
2021-04-10 00:00:00.000 Agent E 12
2021-01-01 00:00:00.000 Agent F 32
2021-02-02 00:00:00.000 Agent F 9
2021-03-10 00:00:00.000 Agent F 11
2021-04-10 00:00:00.000 Agent F 12
2021-01-01 00:00:00.000 Agent G 32
2021-02-02 00:00:00.000 Agent G 0
2021-03-10 00:00:00.000 Agent G 20
2021-04-10 00:00:00.000 Agent G 8
2021-01-01 00:00:00.000 Agent H 32
2021-03-10 00:00:00.000 Agent H 20
2021-04-10 00:00:00.000 Agent H 8
我想获取累计超过 30 次销售的代理商的计数，但如果代理商在过去 45 天内未完成 30 次销售，则计数器（累计逻辑）应重置。
预期输出：
| 年月 | Count_Agent_more_than_30_sales |
|---|---|
| 1月21日 | 5 |
| 2月21日 | 7 |
| 3月21日 | 5 |
| Apr21 | 6 |
逻辑：
- 1 月 21 日 - 5 日，因为 C、E、F、G、H 交叉 30。
- 2 月 21 日 - 7 日，因为 B、C、D、E、F、G、H 累计超过 30。
- 3 月 21 日 - 5 日，因为 A、B、C、D、F 累计超过 30 次。其中 E、G、H 被排除在外，因为距离上一次输入累计超过 30 次销售已经过去 45 天。
- Apr21 - 6，因为 A、B、C、D、E、F 累计超过 30。其中 G、H 被排除在外，因为自上次条目以来已经 45 天累计超过 30 次销售。
我的查询计算一段时间内的总和：
;WITH CTE AS
(
SELECT
CAST(YEAR([DATE]) AS VARCHAR) + ' ' + CAST(MONTH([DATE]) AS VARCHAR) YRMON,
[DATE], AGENT, SUM(SALE) SALES
FROM
agent_sales
GROUP BY
CAST(YEAR([DATE]) AS VARCHAR) + ' ' + CAST(MONTH([DATE]) AS VARCHAR),
AGENT, [DATE]
)
SELECT
*,
SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON) SUMOVERPERIOD
FROM CTE
ORDER BY 3,2
现在我正在尝试将逻辑应用于计算的总和：
;WITH CTE AS (SELECT CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR) YRMON, [DATE], AGENT, SUM(SALE) SALES
FROM agent_sales
GROUP BY CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR), [DATE], 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 3,2
这给了我累计金额。
要检查基于 45 天的累计金额：
;WITH CTE AS (SELECT CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR) YRMON, [DATE], AGENT, SUM(SALE) SALES
FROM agent_sales
GROUP BY CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR), [DATE], 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 CUMULATIVE_ABOVE_30,
[DATE],LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]) [LAG],
DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE]) [DDIFF],
CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 AND DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE])<46 THEN 1 ELSE 0 END AS CUMULATIVE_ABOVE_30_AND_LAST_SALE_IN_45_DAYS
FROM CTE
ORDER BY 3,2
如何获得上述查询以重置具有 45 天逻辑的累积和计数器？例如 - 特工 G 不应该在三月和四月出现。
与上述相同的 SQL，但具有每月代理名称：
;WITH CTE AS (SELECT CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR) YRMON, [DATE], AGENT, SUM(SALE) SALES
FROM agent_sales
GROUP BY CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR), [DATE], AGENT
),
CTE1 as (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 CUMULATIVE_ABOVE_30,
[DATE] AS [DT],LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]) [LAG],
DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE]) [DDIFF],
CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 AND DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE])<46 THEN 1 ELSE 0 END AS CUMULATIVE_ABOVE_30_AND_LAST_SALE_IN_45_DAYS
FROM CTE)
select YRMON,AGENT FROM CTE1 WHERE CUMULATIVE_ABOVE_30_AND_LAST_SALE_IN_45_DAYS=1
与上述相同，但按月计算：
;WITH CTE AS (SELECT CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR) YRMON, [DATE], AGENT, SUM(SALE) SALES
FROM agent_sales
GROUP BY CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR), [DATE], AGENT
),
CTE1 as (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 CUMULATIVE_ABOVE_30,
[DATE] AS [DT],LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]) [LAG],
DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE]) [DDIFF],
CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 AND DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE])<46 THEN 1 ELSE 0 END AS CUMULATIVE_ABOVE_30_AND_LAST_SALE_IN_45_DAYS
FROM CTE)
select YRMON,count(*) FROM CTE1 WHERE CUMULATIVE_ABOVE_30_AND_LAST_SALE_IN_45_DAYS=1
group by YRMON
我无法使用 45 天逻辑查询来重置累积和计数器。
解决方案
注意：我得到不同的结果，可能误解了你的规则之一，但你会明白的。
尝试这个：
DECLARE @ClosingDay int = 21
, @CicleDays int = -45
, @TargetSales int = 30
;
WITH AgentSaleCicle AS
(
SELECT
*
, CicleBegin = DATEADD(DAY, @CicleDays, CONVERT(date, LTRIM(YEAR([date])*10000+MONTH([date])*100+@ClosingDay)))
, CicleEnd = CONVERT(date, LTRIM(YEAR([date])*10000+MONTH([date])*100+@ClosingDay))
FROM [dbo].[agent_sales]
)
, AgentSaleCicleSum AS
(
SELECT
*
-- , CicleDays = DATEDIFF(DAY, CicleBegin, CicleEnd)
, CicleSales = (
SELECT SUM(Sale)
FROM [dbo].[agent_sales] IA
WHERE 1=1
AND IA.Agent = OA.Agent
AND IA.[Date] BETWEEN CicleBegin AND CicleEnd
)
FROM AgentSaleCicle OA
)
SELECT
CicleEnd
, CicleAgentCount = SUM(1)
FROM AgentSaleCicleSum
WHERE CicleSales >= @TargetSales
GROUP BY CicleEnd
ORDER BY CicleEnd
推荐阅读
- r - Plot the values of each column as histogram
- python - Multi column filtering in python data frame
- javascript - How to login via server with sessions, then pass JWT to javascript client
- airflow-scheduler - reseting start_date of an existing airflow dag
- jquery - JQuery doesn't work at times and Im not sure why
- python - Pandas pivot_table 的更快替代品
- azure-storage - 我们如何从 azure 文件共享中获取数据到本地
- java - 如何在 Selenium Java 中获取 DOM 窗口对象
- python - 通过传递 incapsula security- python
- mysql - “INNER JOIN”和“LEFT JOIN”与“GROUP BY”