SQL 的新手.想要将 IF(COUNTIFS()) Excel 公式转换为 SQL 代码，并让 SQL 计算它而不是

Question

我正在运行SQL Server 2008 R2(RTM)。

我有一个 SQL 查询，可以提取日期、产品、客户和单位：

select 
[Transaction Date] as Date, 
[SKU] as Product, 
[Customer Name] as Customer, 
sum(Qty) as Units 
from dataset
where [Transaction Date] < '2019-03-01' and [Transaction Date] >= '2016-01-01'
group by [Transaction Date], [SKU], [Customer Name]
order by [Transaction Date]

这提取了数十万条记录，我想根据以下逻辑确定某个事务是新订单还是重新排序：

重新订购：该特定客户在过去 6 个月内订购了该特定产品

新订单：该特定客户在过去 6 个月内未订购过该特定产品

为此，我在 Excel 中有一个似乎有效的公式：

=IF(COUNTIFS(A$1:A1,">="&DATE(YEAR(A2),MONTH(A2)-6,DAY(A2)),C$1:C1,C2,B$1:B1,B2),"Reorder","New Order")

当我单独粘贴它或将其粘贴到较小的数据集中时，该公式有效，但是当我尝试将其复制粘贴到所有 500K+ 行时，Excel 放弃了，因为它会循环进行每次计算。

这可能可以在SQL中完成，但是我不知道如何将此Excel公式转换为SQL，我刚刚开始研究它。

Answer 1

你在那里开始查询做得很好。 您希望向查询添加三个附加函数。

您需要的第一件事是最简单的。GETDATE()仅返回当前日期。 当您将当前日期与交易日期进行比较时，您将需要它。

第二个功能是DATEDIFF，它将为您提供两个日期(月、日、年、季度等)之间的时间单位。 使用DATEDIFF，您可以说"这个日期是过去 6 个月内的日期"。 这种格式非常简单。 这是DATEDIFF(interval, date1, date2).

您正在寻找的 thrid 函数是CASE，它允许您告诉 SQL 如果满足一个条件，则给你一个答案，但如果满足不同的条件，则给出不同的答案。 对于您的示例，您可以说"如果天数差为 <60，则返回'重新排序'，如果不给我'新订单'"。

把所有的东西放在一起：

SELECT CASE 
WHEN DATEDIFF(MONTH, [Transaction Date], GETDATE()) <= 6
THEN 'Reorder'
ELSE 'New Order'
END as ORDER_TYPE
,[Transaction Date] AS DATE
,[SKU] AS PRODUCT
,[Customer Name] AS CUSTOMER
,Qty AS UNITS
FROM DATASET

• 有关CASE的其他示例，请查看此站点：https://www.w3schools.com/sql/sql_ref_case.asp
• 有关 DATEDIFF 的其他示例，请查看此处：请参阅 以下网页示例和尝试机会： https://www.w3schools.com/sql/func_sqlserver_datediff.asp

Answer 2

SELECT CASE
WHEN Datediff(day, [transaction date], Getdate()) <= 180 THEN 'reorder'
ELSE 'Neworder'
END,
[transaction date] AS Date,
[sku]              AS Product,
[customer name]    AS Customer,
qty                AS Units
FROM   datase  

Answer 3

如果我理解正确，您想在前一天达到峰值并进行比较。 这表明lag()：

select (case when lag([Transaction Date]) over (partition by SKU, [Customer Name] order by [Transaction Date]) >
dateadd(month, -6, [Transaction Date])
then 'Reorder'
else 'New Order'
end) as Order_Type
[Transaction Date] as Date, 
[SKU] as Product, 
[Customer Name] as Customer, 
sum(Qty) as Units 
from dataset d
group by [Transaction Date], [SKU], [Customer Name];

编辑：

在 SQL Server 2008 中，您可以使用以下OUTER APPLY模拟LAG()：

select (case when dprev.[Transaction Date] >
dateadd(month, -6, d.[Transaction Date])
then 'Reorder'
else 'New Order'
end) as Order_Type
d.[Transaction Date] as Date, 
d.[SKU] as Product, 
d.[Customer Name] as Customer, 
sum(d.Qty) as Units 
from dataset d outer apply
(select top (1) dprev.*
from dataset dprev
where dprev.SKU = d.SKU and
dprev.[Customer Name] = d.[Customer Name] and
dprev.[Transaction Date] < d.[Transaction Date]
order by dprev.[Transaction Date] desc
) dprev
group by d.[Transaction Date], d.[SKU], d.[Customer Name];