时间戳

首页 > 解决方案 > 为什么 MySQL SUM IF 在 2021 年退出工作

mysql - 为什么 MySQL SUM IF 在 2021 年退出工作

问题描述

此代码在 2020 年工作后于 2021 年退出工作：

SELECT name, SUM(CASE WHEN YEARWEEK(prod_date) = YEARWEEK(now()- INTERVAL 1 WEEK) THEN gas_prod ELSE NULL END) AS LastWeek FROM daily_prod GROUP BY name

该代码现在计算的数字比上周发生的要大。为什么新的一年会导致此代码以不同的方式工作？

标签： mysql

解决方案

首先，在这里使用 sum(case/when) 是一个不好的选择。您的查询将针对您的整个daily_prod 表，每条记录，但仅在基于当前日期的给定yeardate() 期间内求和。与其这样做，不如构建一个 WHERE 子句来获取您关心的记录。YearWeek() 函数基于周日到周六的周计划。因此，例如本周是从 1 月 31 日凌晨 12:00（午夜）到但不包括 2 月 7 日午夜 12:00。这包括截至 2 月 6 日晚上 11:59:59 的所有内容。

因此，通过执行嵌入式 MySQL 变量，您可以构建为“来自”表别名，正如我将展示的那样。首先，让我们获取当前的 NOW() 包括小时/分钟/秒的任何内容，然后只删除日期部分。例如： 2021-02-04 @ 01:42am 截断为仅 2021-02-04 12:00am

select cast( now() as date )

从那以后，现在，我们需要去一周的第一天，表示为星期日。为此，我们使用星期几并减去 1，因此它是一个从零开始的值，其中星期日 = 0，星期六 = 6。所以星期四，通常返回 5，减去 1 = 4。2 月 4 日 - 4 天 = 1 月 31 日这是本周的第一天。

select date_sub( [result of sql above], interval dayofweek( [result of sql above] ) -1 day )

现在，由于您想要整周的数据，在这种情况下，您的数据将是从 1 月 31 日上午 12:00 到 2 月 6 日（星期六）晚上 11:59:59。因此，将上述一周的开始时间加上 1 周，将您带到 2 月 7 日。

select  date_add( [result of 2nd query], interval 1 week )

是的，这是故意的，因为现在在您的最终查询中，您可以查询少于 2 月 7 日。

因此，通过在数据源上使用 where，您只能获取这些记录，并且，如果您的表在交易日期有索引，则可以针对查询进行优化。要使用内联变量，我们可以只使用这些结果作为查询的一部分，例如：

SELECT 
      dp.name, 
      SUM( dp.gas_prod ) CurrwntWeekGas 
   FROM 
      daily_prod dp,
      ( select
               -- first, variable @nd = Now as a date only
               @nd := cast( now() as date ),
               -- from the @nd, subtract 0-based day of week
               -- example: Thursday is 5th day of week -1 = 4 days to subtract
               @fow := date_sub( @nd, interval dayofweek( @nd ) -1 day ),
               -- finally from the first of the week, add 1 week to the END point for the query
               @nextWeek := date_add( @fow, interval 1 week )
      ) sqlvars
   where
         -- only grab records greater or equal to the first day of the week
          dp.prod_date >= @fow
      -- and ALSO LESS then the the beginning of NEXT week
      AND dp.prod_date < @nextWeek
   GROUP BY 
      dp.name

因此，在处理 from 子句别名“sqlvars”时，它会创建 3 个“@”变量来定义一周的开始和下一周的开始值。然后可以将这些应用于 where 子句并仅限制您需要的记录，而不是整个表。

如果您真的想要从您所在的那一周开始的前一周的结果......例如：自本周以来从 1 月 24 日到 1 月 30 日的总数尚未完成，那么只需将您的 dayofweek() -1 更改为 dayofweek() -8 获得整个前一周，而不是您现在中间的当前一周。

推荐阅读