MySQL 按周分组

Question

我有大量交易日期时间字段可以追溯到几年前的记录。我想对今年和去年的同一时间跨度进行比较分析。如何在 3 个月的范围内按周分组？

由于 2012 年的开始日期与 2011 年的开始日期不同，我在使用 YEARWEEK 和 WEEK 函数时遇到了问题。

鉴于我每天都有从 1 月 1 日到今天的日期时间记录，并且记录的日期时间与前一年相同，我如何按周分组以便输出是日期总和，例如:01/01/2011 、01/08/2011、01/15/2011 等，以及 01/01/2012、01/08/2012、01/15/2012 等？

到目前为止我的查询如下:

SELECT 
    DATE_FORMAT(A.transaction_date, '%Y-%m-%d') as date,
    ROUND(sum(A.quantity), 3) AS quantity,
    ROUND(sum(A.total_amount), 3) AS amount,
    A.product_code, 
    D.fuel_type_code, 
    D.fuel_type_name, 
    C.customer_code, 
    C.customer_name 
FROM 
    cl_transactions AS A
INNER JOIN 
    card AS B ON A.card_number=B.card_number 
INNER JOIN 
    customer AS C ON B.customer_code=C.customer_code 
INNER JOIN 
    fuel_type AS D ON A.fuel_type=D.fuel_type_code 
WHERE 
    ((A.transaction_date >= DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y-%m-01')) OR (A.transaction_date - INTERVAL 1 YEAR >=  DATE_FORMAT(NOW() - INTERVAL 15 MONTH, '%Y-%m-01') AND A.transaction_date <= NOW() - INTERVAL 1 YEAR))
GROUP BY 
    A.transaction_date, fuel_type_code;

我基本上想要实现以下伪查询的东西:

GROUP BY 
    STARTING FROM THE OLDEST DATE (A.transaction_date + INTERVAL 6 DAY)

Answer 1

我从一个内部查询开始，使用 sqlvariables 构建今年和去年每个相应开始年/月/日的起始/终止范围(例如:分别为 2012-01-01 和 2011-01-01) .由此，我还预先格式化了最终输出的日期，这样您就有了一个主日期基础来显示，无论“今年”是哪一周。

由此，我连接到交易表，其中交易日期介于本周开始和下周开始之间。由于日期/时间戳包括小时分钟，因此 2012-01-01 本身隐含为一天中的 12:00:00am(午夜)。之间将在 7 天后的中午 12:00:00 开始。该日期将成为下一周的开始日期。

因此，通过在去年或今年时间段之间的日期加入，它具有相同的团体资格。因此，字段选择在去年或今年分别执行 ROUND( SUM( IF() ))。如果传入交易日期小于当年的一周开始，则它必须是前一年的记录，否则是当年的记录。因此，分别添加值本身，或应用零。

现在，您有了分组依据。它符合条件的星期已经通过“ThisYearWeekOf”格式化列从内部查询中准备好了，而不管另外计算的“YEARWEEK()”或“WEEK()”。日期范围为我们提供了资格。

最后，我将燃料类型添加为连接并将其包含为分组依据。对于正确的 SQL，您必须按所有非聚合列进行分组，尽管 MySQL 允许您通过获取给定组的第一个条目(如果在分组依据中未如此指定)来完成。

最后，我确实包含了客户的信息，因为您在分组依据中没有这些信息，而且似乎不适用……它只会任意抓取一个。但是，我已将其添加到分组依据中，因此现在您的记录将按客户级别、按产品和燃料类型显示今年和去年之间的销售额和数量。

SELECT
      JustWeekRange.ThisYearWeekOf,
      CTrans.product_code,
      FT.fuel_type_code, 
      FT.fuel_type_name, 
      C.customer_code, 
      C.customer_name,
      ROUND( SUM( IF( CTrans.transaction_date < JustWeekRange.ThisYrWeekStart, CTrans.Quantity, 0 )), 3) as LastYrQty,
      ROUND( SUM( IF( CTrans.transaction_date < JustWeekRange.ThisYrWeekStart, CTrans.total_amount, 0 )), 3) as LastYrAmt,
      ROUND( SUM( IF( CTrans.transaction_date < JustWeekRange.ThisYrWeekStart, 0, CTrans.Quantity )), 3) as ThisYrQty,
      ROUND( SUM( IF( CTrans.transaction_date < JustWeekRange.ThisYrWeekStart, 0, CTrans.total_amount )), 3) as ThisYrAmt,
   FROM 
      ( SELECT 
                 DATE_FORMAT(@ThisYearDate, '%Y-%m-%d') as ThisYearWeekOf,
                 @LastYearDate as LastYrWeekStart,
                 @ThisYearDate as ThisYrWeekStart,
                 @LastYearDate := date_add( @LastYearDate, interval 7 day ) LastYrStartOfNextWeek,
                 @ThisYearDate := date_add( @ThisYearDate, interval 7 day ) ThisYrStartOfNextWeek
            FROM 
                 (select @ThisYearDate := '2012-01-01',
                         @LastYearDate := '2011-01-01' ) sqlvars,
                 cl_transactions justForLimit
            HAVING
               ThisYrWeekStart < '2012-04-01'
            LIMIT 15 ) JustWeekRange

      JOIN cl_transactions AS CTrans
         ON    CTrans.transaction_date BETWEEN 
               JustWeekRange.LastYrWeekStart AND JustWeekRange.LastYrStartOfNextWeek
           OR  CTrans.transaction_date BETWEEN 
               JustWeekRange.ThisYrWeekStart AND JustWeekRange.ThisYrStartOfNextWeek

      JOIN fuel_type FT
         ON CTrans.fuel_type = FT.fuel_type_code

      JOIN card 
         ON CTrans.card_number = card.card_number 
         JOIN customer AS C 
            ON card.customer_code = C.customer_code 

   GROUP BY
      JustWeekRange.ThisYearWeekOf,
      CTrans.product_code,
      FT.fuel_type_code, 
      FT.fuel_type_name,
      C.customer_code, 
      C.customer_name