如何按星期几关联2023年2月与2022年2月的数据？

我正在找更优雅的方案来关联2023年2月和2022年2月的数据。已知2023年2月1日是周三，2022年2月的第一个周三是2022-02-02，希望按「星期几+该月第几个星期几」的规则匹配两年数据，得到如下格式的结果：

2023 table:
date        dayoftheweek    people
2023-02-01  Wednesday       4
2023-02-02  Thursday        17
2023-02-03  Friday          4
2023-02-04  Saturday        0
2023-02-05  Sunday          22
2023-02-06  Monday          33
2023-02-07  Tuesday         12
2023-02-08  Wednesday       3
…       
2023-02-28  Tuesday         45



2022 table:
date        dayoftheweek    people
2022-02-01  Tuesday         14
2022-02-02  Wednesday       19
2022-02-03  Thursday        12
2022-02-04  Friday          18
2022-02-05  Saturday        14
2022-02-06  Sunday          19
2022-02-07  Monday          0
2022-02-08  Tuesday         7
2022-02-09  Wednesday       9
…       
2022-02-28  Monday          8


desired result:
date          dayofthweek   2023    2022
2023-02-01  Wednesday       4       19
2023-02-02  Thursday        17      12
2023-02-03  Friday          4       18
2023-02-04  Saturday        0       14
2023-02-05  Sunday          22      19
2023-02-06  Monday          33      0
2023-02-07  Tuesday         12      7
2023-02-08  Wednesday       3       9
…           
2023-02-28  Tuesday         45  

我已经写出了可行的SQL语句，运行速度快且能得到预期结果，但想寻求更简洁高效的实现方式：

SET @mon_last_year:= 0;
SET @tue_last_year:= 0;
SET @wed_last_year:= 0;
SET @thu_last_year:= 0;
SET @fri_last_year:= 0;
SET @sat_last_year:= 0;
SET @sun_last_year:= 0;

SET @mon_this_year:= 0;
SET @tue_this_year:= 0;
SET @wed_this_year:= 0;
SET @thu_this_year:= 0;
SET @fri_this_year:= 0;
SET @sat_this_year:= 0;
SET @sun_this_year:= 0;


  
  SELECT
  fecha2023,
  day_name_count_this_year,
  fecha2022,
  day_name_count_last_year,
  total_this_year,
  total_last_year
  FROM
  (
    SELECT
    fecha AS 'fecha2023',
    CONCAT(day_name,day_count) AS day_name_count_this_year,
    total AS total_this_year
    FROM
    (
      SELECT
      fecha,
      DATE_FORMAT(fecha,'%W') AS day_name,
      CASE 
        WHEN DAYOFWEEK(fecha) = 1 THEN @sun_this_year:= @sun_this_year+1
        WHEN DAYOFWEEK(fecha) = 2 THEN @mon_this_year:= @mon_this_year+1
        WHEN DAYOFWEEK(fecha) = 3 THEN @tue_this_year:= @tue_this_year+1
        WHEN DAYOFWEEK(fecha) = 4 THEN @wed_this_year:= @wed_this_year+1
        WHEN DAYOFWEEK(fecha) = 5 THEN @thu_this_year:= @thu_this_year+1
        WHEN DAYOFWEEK(fecha) = 6 THEN @fri_this_year:= @fri_this_year+1
        WHEN DAYOFWEEK(fecha) = 7 THEN @sat_this_year:= @sat_this_year+1
      END AS day_count, 
      total
      FROM
      (
        SELECT
        fecha,
        SUM(total_gasto) AS total
        FROM
        gastos
        WHERE
        fecha >= '2023-02-01'
        AND
        fecha <= '2023-02-28'
        GROUP BY fecha
      ) ty_
    ) ty_
  ) ty_

  LEFT JOIN
  
  (
    SELECT
    fecha AS 'fecha2022',
    CONCAT(day_name,day_count) AS day_name_count_last_year,
    total AS total_last_year
    FROM
    (
      SELECT
      fecha,
      DATE_FORMAT(fecha,'%W') AS day_name,
      CASE 
        WHEN DAYOFWEEK(fecha) = 1 THEN @sun_last_year:= @sun_last_year+1
        WHEN DAYOFWEEK(fecha) = 2 THEN @mon_last_year:= @mon_last_year+1
        WHEN DAYOFWEEK(fecha) = 3 THEN @tue_last_year:= @tue_last_year+1
        WHEN DAYOFWEEK(fecha) = 4 THEN @wed_last_year:= @wed_last_year+1
        WHEN DAYOFWEEK(fecha) = 5 THEN @thu_last_year:= @thu_last_year+1
        WHEN DAYOFWEEK(fecha) = 6 THEN @fri_last_year:= @fri_last_year+1
        WHEN DAYOFWEEK(fecha) = 7 THEN @sat_last_year:= @sat_last_year+1
      END AS day_count, 
      total
      FROM
      (
        SELECT
        fecha,
        SUM(total_gasto) AS total
        FROM
        gastos
        WHERE
        fecha >= '2022-02-01'
        AND
        fecha <= '2022-02-28'
        GROUP BY fecha
      ) ly_
    ) ly_
  ) ly_
  ON(day_name_count_this_year = day_name_count_last_year)

优化后的实现方案 #

可以利用CTE（公共表表达式）和窗口函数替代大量用户变量，让代码更简洁易读，逻辑更直观：

WITH this_year_data AS (
    SELECT
        fecha AS fecha2023,
        DATE_FORMAT(fecha, '%W') AS day_of_week,
        SUM(total_gasto) AS `2023`,
        ROW_NUMBER() OVER (PARTITION BY DAYOFWEEK(fecha) ORDER BY fecha) AS week_order
    FROM gastos
    WHERE fecha BETWEEN '2023-02-01' AND '2023-02-28'
    GROUP BY fecha
),
last_year_data AS (
    SELECT
        DATE_FORMAT(fecha, '%W') AS day_of_week,
        SUM(total_gasto) AS `2022`,
        ROW_NUMBER() OVER (PARTITION BY DAYOFWEEK(fecha) ORDER BY fecha) AS week_order
    FROM gastos
    WHERE fecha BETWEEN '2022-02-01' AND '2022-02-28'
    GROUP BY fecha
)
SELECT
    ty.fecha2023 AS date,
    ty.day_of_week,
    ty.`2023`,
    ly.`2022`
FROM this_year_data ty
LEFT JOIN last_year_data ly
    ON ty.day_of_week = ly.day_of_week
    AND ty.week_order = ly.week_order
ORDER BY ty.fecha2023;

优化说明

1. 结构简化：用CTE拆分两年的数据逻辑，替代多层嵌套子查询，代码层级更清晰
2. 变量替代：用ROW_NUMBER() OVER (PARTITION BY DAYOFWEEK(fecha) ORDER BY fecha)自动计算每个星期几在当月的出现次数，省去14个用户变量的定义和维护
3. 结果对齐：最终输出列名和期望的结果完全匹配，无需额外调整
4. 性能持平：窗口函数的计算效率和原用户变量方案相当，不会影响查询速度

内容的提问来源于stack exchange，提问作者Felipe Tejeda