SQL Server 2008 R2 - 计算以前出现的按日期范围拆分的值

Question

以下是我们对营销部门对过去 90 天内收到的潜在客户的临时请求所做的简单查询。

SELECT ID
    ,FIRST_NAME
    ,LAST_NAME
    ,ADDRESS_1
    ,ADDRESS_2
    ,CITY
    ,STATE
    ,ZIP
    ,HOME_PHONE
    ,MOBILE_PHONE
    ,EMAIL_ADDRESS
    ,ROW_ADDED_DTM
FROM WEB_LEADS
WHERE ROW_ADDED_DTM BETWEEN @START AND @END

他们要求添加更多派生列，以显示EMAIL_ADDRESS匹配的ADDRESS_1的先前出现次数。但他们想要的是不同的日期范围。

因此，派生列将如下所示：

,COUNT_ADDRESS_1_LAST_1_DAYS,
,COUNT_ADDRESS_1_LAST_7_DAYS
,COUNT_ADDRESS_1_LAST_14_DAYS
etc.

当只有几个时，我使用更新语句手动填充了这些派生列。上面的查询实际上只是一个包含更多列的更大查询的示例。实际请求已扩展到 13 列的 6 个日期范围。我问是否有比使用 78 个附加更新语句更好的方法。

Answer 1

我认为如果不实际创建一个对不同选择进行硬编码的查询，您将很难编写包含每个电子邮件地址的所有 78 个指标的查询。但是，您可以使用动态 SQL 生成此类透视查询，这将为您节省一些击键时间，并在您向表中添加更多列时动态调整。

你想要得到的结果将看起来像这样（但当然你不想输入它）：

;WITH y AS
(
  SELECT 
    EMAIL_ADDRESS,
/* aggregation portion */
    [ADDRESS_1] = COUNT(DISTINCT [ADDRESS_1]),
    [ADDRESS_2] = COUNT(DISTINCT [ADDRESS_2]),
    ... other columns
/* end agg portion */
    FROM dbo.WEB_LEADS AS wl 
    WHERE ROW_ADDED_DTM >= /* one of 6 past dates */
    GROUP BY wl.EMAIL_ADDRESS
)
SELECT EMAIL_ADDRESS,
/* pivot portion */
  COUNT_ADDRESS_1_LAST_1_DAYS = *count address 1 from 1 day ago*,
  COUNT_ADDRESS_1_LAST_7_DAYS = *count address 1 from 7 days ago*,
  ... other date ranges ...
  COUNT_ADDRESS_2_LAST_1_DAYS = *count address 2 from 1 day ago*,
  COUNT_ADDRESS_2_LAST_7_DAYS = *count address 2 from 7 days ago*,
  ... other date ranges ...
  ... repeat for 11 more columns ...
/* end pivot portion */
FROM y 
GROUP BY EMAIL_ADDRESS
ORDER BY EMAIL_ADDRESS;

这有点复杂，它应该作为一个脚本运行，但我将把它分成几个块，穿插关于如何填充上述部分的评论，而无需键入它们。（不久之后，@Bluefeet可能会有一个更好的PIVOT替代方案。我将在/* */中附上我穿插的评论，以便您仍然可以将此答案的大部分复制到管理工作室中，并在评论完好无损的情况下运行它。

要复制的代码/注释如下：

---

/*首先，让我们构建一个日期表，该表既可用于派生用于透视的标签，也可用于协助聚合。我已经添加了您提到的三个范围，并在第四个范围中猜测，但希望很清楚如何添加更多：*/

DECLARE @d DATE = SYSDATETIME();
CREATE TABLE #L(label NVARCHAR(15), d DATE);
INSERT #L(label, d) VALUES
(N'LAST_1_DAYS',  DATEADD(DAY,   -1,  @d)),
(N'LAST_7_DAYS',  DATEADD(DAY,   -8,  @d)),
(N'LAST_14_DAYS', DATEADD(DAY,   -15, @d)),
(N'LAST_MONTH',   DATEADD(MONTH, -1,  @d));

/*接下来，让我们生成每个列名重复的查询部分。首先，聚合部分的格式仅为 col = COUNT(DISTINCT col) 。我们将转到目录视图以动态派生列名列表（ID、EMAIL_ADDRESS 和 ROW_ADDED_DTM 除外），并将它们填充到 #temp 表中以供重用。*/

SELECT name INTO #N FROM sys.columns
WHERE [object_id] = OBJECT_ID(N'dbo.WEB_LEADS')
AND name NOT IN (N'ID', N'EMAIL_ADDRESS', N'ROW_ADDED_DTM');
DECLARE @agg NVARCHAR(MAX) = N'', @piv NVARCHAR(MAX) = N'';
SELECT @agg += ',
  ' + QUOTENAME(name) + ' = COUNT(DISTINCT ' 
  + QUOTENAME(name) + ')' FROM #N;
PRINT @agg;

/*接下来，我们将构建"枢轴"部分（即使我正在钓鱼穷人的枢轴 - 一堆CASE表达式）。对于每个列名，我们需要针对每个范围一个条件，因此我们可以通过将列名列表与我们的标签表交叉联接来实现这一点。（稍后我们将在查询中再次使用这种确切的技术，以使/* one of past 6 dates */部分正常工作。*/

SELECT @piv += ',
  COUNT_' + n.name + '_' + l.label
  + ' = MAX(CASE WHEN label = N''' + l.label 
  + ''' THEN ' + QUOTENAME(n.name) + ' END)'
FROM #N as n CROSS JOIN #L AS l;
PRINT @piv;

/*现在，按照我们想要的方式填充这两个部分，我们可以构建一个动态 SQL 语句来填充其余部分：*/

DECLARE @sql NVARCHAR(MAX) = N';WITH y AS
(
    SELECT 
      EMAIL_ADDRESS, l.label' + @agg + '
      FROM dbo.WEB_LEADS AS wl 
      CROSS JOIN #L AS l
      WHERE wl.ROW_ADDED_DTM >= l.d
      GROUP BY wl.EMAIL_ADDRESS, l.label
)
SELECT EMAIL_ADDRESS' + @piv + '
FROM y 
GROUP BY EMAIL_ADDRESS
ORDER BY EMAIL_ADDRESS;';
PRINT @sql;
EXEC sp_executesql @sql;
GO
DROP TABLE #N, #L;

/*现在，这是一段非常复杂的代码，也许可以通过PIVOT使它变得更容易。但我认为即使@Bluefeet也会编写一个使用动态 SQL 的 PIVOT 版本，因为恕我直言，这里要硬编码的东西太多了。*/