时间戳

首页 > 解决方案 > 如何遍历日期、30 次以查找时间点的历史信息？

sql - 如何遍历日期、30 次以查找时间点的历史信息？

问题描述

我试图找出过去不同时间点的客户交易详情。我提出了一个查询，但我每次都必须在声明语句中更改日期。有没有办法遍历日期以获取过去 x 天的数据？

DECLARE @StartDate AS Date 
SET @StartDate = '2018-10-30'

SELECT  u.member_id         AS Member_id
    ,CAST(MAX(pe.executed_time) AS Date) AS Max_Date
    ,DATEDIFF(dd,@StartDate,MAX(pe.executed_time))*-1+1 AS R
    ,COUNT(*)               AS F
    ,SUM(pi.Price)          AS M
    FROM [user] AS u
    LEFT JOIN purchase_entry    AS pe
    ON u.member_id=pe.member_id
    LEFT JOIN purchase_item AS pi
    ON pe.order_id=pi.order_id
    WHERE 1=1
        AND pe.[status] = 'purchase'
        AND pe.executed_time <= @StartDate -- Change the declared Date to have historical information.
    GROUP BY u.id, u.member_id
    ORDER BY Recency

例如，我需要一种方法让我的查询在过去 30 天内循环遍历 @StartDate。例如：@StartDate = '2018-11-30' 然后 @StartDate '2018-11-29' ，依此类推...

标签： sqlsql-server

解决方案

您可以列出您想要的日期：

WITH dates as (
      SELECT v.*
      FROM (VALUES (CONVERT(DATE, '2018-11-30')), (CONVERT(DATE, '2018-11-29'))
         ) v(dte)
     )
SELECT dates.dte, u.member_id, . . .
FROM dates CROSS JOIN
     [user] u JOIN
     purchase_entry pe
     ON u.member_id = pe.member_id LEFT JOIN
     purchase_item pi
     ON pe.order_id = pi.order_id
WHERE pe.[status] = 'purchase' AND
      pe.executed_time <= date.dte -- Change the declared Date to have historical information.
GROUP BY v.dte, u.id, u.member_id
ORDER BY Recency;

对于特定的日期时间段，您可以使用递归 CTE 构造它们：

with dates as (
      select convert(date, '2018-11-30') as dte, 1 as n
      union all
      select dateadd(day, -1, date.dte), n + 1
      from dates
      where n < 30
     )
. . .

推荐阅读