主页 / dba / 问题 / 283286
Accepted
TheDemonLord
Asked: 2021-01-15 17:27:47 +0800 CST

计数存在于之前而不存在于之后和之后但不存在于之前的行数

  • 772

我有一个 Azure SQL 数据库，一个包含 3 个相关列的表：品牌、ID 和月/年，还有一个临时表，它简单地列出了带有 ID 的月/年。

该表显示了该给定月份的所有活动 ID 的列表。

Month/Year 是一个日期戳列，日期为月份的第一个。ID 列只是一个 ID。

目标是有一个输出，每个月将显示本月活跃但下个月不活跃的所有记录的计数（流失）以及上个月不活跃但活跃的 ID在这个月（新），并按品牌和月份分组，所以：

品牌 | 流失 | 新 | 第 1 个月
| 10 | 12 | 2019-12-01
2 | 11 | 9 | 2019-12-01

我还应该注意到 Table1 有 1900 万行。

我尝试了以下代码：

Declare @Id int
declare @my date

set @Id = 300
set @my = '2019-12-01'

select t1.[brand],
count(c.[ID]) as [churn],
count(n.[ID]) as [new],
t1.[month/year]
from Table1 T1

left join Table1 c on
c.[ID] = t1.[ID] and c.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id +1)))
left join Table1 n on
n.[ID] = T1.[ID] and n.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id -1)))

where t1.[Month/Year] = @my
group by t1.[Brand], t1.[Month/Year]

尽管它产生了输出，但我认为它不正确，而且花了很长时间，这可能是由于我在联接中自由使用了子查询。

我的问题是 2 倍 - 有人可以帮助以更好的方式计算行数并加入它们吗？有没有更好的方法可以在没有这么多子查询的情况下做到这一点？

sql-server join

2 个回答

  1. Best Answer

    通常，您应该包含生成表模式并用数据填充它们的脚本。（同样对于性能问题，您也应该包括执行计划。）如果没有真实数据，我无法进行大量测试，但我相信此查询应该会提高您所看到的性能：

    DECLARE @my DATE = '2019-12-01'
DECLARE @nextMonthYear DATE = DATEADD(MONTH, 1, @my)
DECLARE @prevMonthYear DATE = DATEADD(MONTH, -1, @my)

SELECT CurrentMonth.Brand, SUM(CASE WHEN NextMonth.ID IS NULL THEN 1 ELSE 0 END) AS Churn, SUM(CASE WHEN PreviousMonth.ID IS NULL THEN 1 ELSE 0 END) AS New, CurrentMonth.MonthYear
FROM Table1 AS CurrentMonth
LEFT JOIN Table1 AS NextMonth
    ON NextMonth.MonthYear = @nextMonthYear
    AND CurrentMonth.ID = NextMonth.ID  
LEFT JOIN Table1 AS PreviousMonth
    ON PreviousMonth.MonthYear = @prevMonthYear
    AND CurrentMonth.ID = PreviousMonth.ID  
WHERE CurrentMonth.MonthYear = @my
GROUP BY CurrentMonth.Brand, CurrentMonth.MonthYear

    这也比一堆子查询简单得多（开发人员可以阅读和 SQL Server 解析）。您可能是对的，不需要的额外加入子查询是您的性能问题的根源（但我无法在没有看到执行计划的情况下验证这一点）。

    上面的查询所做的是LEFT JOIN在其自身上使用 a to Table1 两次（一次用于 the PreviousMonth，一次用于 the NextMonth），ID以获取与 . 匹配和不匹配的所有记录CurrentMonth。然后使用内部SUM()带有CASE语句的聚合函数，我们可以只总结NULLs（在andID中CurrentMonth不存在的情况）以获得 and 的计数，并按and分组。PreviousMonthNextMonthNewChurnBrandMonthYearCurrentMonth

    • 1

  2. 如果DAY(date)总是 1 那么你可以使用类似的东西

    SELECT *, 
       CASE WHEN LAG([id]) OVER (ORDER BY [Month/Year] RANGE BETWEEN FOLLOWING 1 MONTH AND FOLLOWING 1 MONTH) IS NULL 
            THEN 'New' 
            WHEN LEAD([id]) OVER (ORDER BY [Month/Year] RANGE BETWEEN FOLLOWING 1 MONTH AND FOLLOWING 1 MONTH) IS NULL 
            THEN 'Churn'
            ELSE '' 
            END AS [state]
FROM [src table]

    如果您只需要计算它们，则在 CTE 中使用与上述类似的查询，并将其聚合到主查询中。

    • 0

相关问题