【LeetCode 2994. 发生在周五的交易 II】[MySQL with recursive/T-SQL master.dbo.spt_values/Oracle CONNECT BY]生成2023-11月所有周五的日期

目录

• 题目地址
• 说明
• 代码
  • • MySQL
    • T-SQL
    • Oracle

题目地址

https://leetcode.cn/problems/friday-purchases-ii/description/

说明

以下代码涉及的要点：

• 不同DB dialect，使用WEEKOFMONTH功能
• 不同DB dialect，使用DAYOFWEEK功能
• 不同DB dialect，生成某年某月份所有星期几

代码

MySQL

# Write your MySQL query statement below

WITH RECURSIVE Fridays (week_of_month, purchase_date) AS (
  -- Initial query to find the first Friday of the month
  SELECT 
    1 AS week_of_month,
    (SELECT DATE_ADD(DATE_ADD(LAST_DAY(CONCAT(@year, '-', LPAD(@month, 2, '0'), '-', '01')) + INTERVAL 1 DAY, INTERVAL -1 MONTH),
                     INTERVAL (@week_flag - WEEKDAY(DATE_ADD(LAST_DAY(CONCAT(@year, '-', LPAD(@month, 2, '0'), '-', '01')) + INTERVAL 1 DAY, INTERVAL -1 MONTH))) DAY)) AS purchase_date
  
  UNION ALL
  
  -- Recursive query to find all subsequent Fridays of the month
  SELECT
    week_of_month + 1,
    DATE_ADD(purchase_date, INTERVAL 7 DAY)
  FROM Fridays,
  (select @year := '2023', @month := 11  -- 生成该年份该月份所有@week_flag表示的星期几
   ,@week_flag:= 4      -- 0表示周一，1表示周二，2表示周三，3表示周四， 4表示周五，5表示周六，6表示周日
  ) vars    
  WHERE MONTH(DATE_ADD(purchase_date, INTERVAL 7 DAY)) = @month
)
,
t1 as(
    SELECT
    *,
    DAYOFMONTH(purchase_date) as day_of_month,
    DAYOFWEEK(purchase_date) as day_of_week,
    FLOOR((DAYOFMONTH(purchase_date) + DAYOFWEEK(CONCAT(YEAR(purchase_date), '-', MONTH(purchase_date), '-01')) - 2) / 7) + 1 as week_of_month
    FROM
    Purchases
)
,
t2 as(
    select week_of_month,  purchase_date,
    sum(ifnull(amount_spend,0)) as total_amount
    from t1
    where day_of_week=6 #周五
    and date_format(purchase_date, "%Y-%m") ="2023-11"
    group by week_of_month,  purchase_date
    order by week_of_month asc
)


-- Final selection to get all Fridays of November 2023
SELECT f.week_of_month, f.purchase_date,
ifnull(t2.total_amount ,0) as total_amount
FROM Fridays f
left join t2
on f.week_of_month=t2.week_of_month and f.purchase_date=t2.purchase_date
order by f.week_of_month asc

-- 下面的代码供调试使用
# select * from Fridays

T-SQL

T-SQL里好像没有with recursive关键字(有类似with recursive的功能，直接with as做就行，select的时候得在最后加OPTION (MAXRECURSION 0); )

 
 
 

你也可以基于 master.dbo.spt_values 这张表生成2023-11所有的日期，然后过滤一下得到2023-11所有周五的日期。

-- Generate all days of 2023-11
WITH AllDays AS (
    SELECT 
        DATEADD(DAY, number, '2023-11-01') AS purchase_date
    FROM master.dbo.spt_values
    WHERE type = 'P' 
        AND number >= 0 
        AND number < DATEDIFF(DAY, '2023-11-01', '2023-12-01')
)

-- Filter Fridays
, Fridays AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY purchase_date) AS week_of_month,
        purchase_date
    FROM AllDays
    WHERE DATENAME(WEEKDAY, purchase_date) = 'Friday'
)

-- Calculate total amount spent on Fridays in November 2023
, t1 AS (
    SELECT
        *,
        DAY(purchase_date) AS day_of_month,
        DATEPART(WEEKDAY, purchase_date) AS day_of_week,
        FLOOR((DAY(purchase_date) + DATEPART(WEEKDAY, CAST(YEAR(purchase_date) AS VARCHAR) + '-' + CAST(MONTH(purchase_date) AS VARCHAR) + '-01') - 2) / 7) + 1 AS week_of_month
    FROM
        Purchases
    WHERE
        MONTH(purchase_date) = 11
)

, t2 AS (
    SELECT
        week_of_month,
        purchase_date,
        SUM(ISNULL(amount_spend, 0)) AS total_amount
    FROM
        t1
    WHERE
        day_of_week = 6 -- Friday
    GROUP BY
        week_of_month,
        purchase_date
)

-- Final selection to get all Fridays of November 2023
SELECT
    f.week_of_month,
    FORMAT(f.purchase_date , 'yyyy-MM-dd') as purchase_date,
    ISNULL(t2.total_amount, 0) AS total_amount
FROM
    Fridays f
LEFT JOIN
    t2 ON f.week_of_month = t2.week_of_month AND f.purchase_date = t2.purchase_date
ORDER BY
    f.week_of_month;

Oracle

Oracle里好像没有with recursive，你可以使用CONNECT BY来做。

-- Generate all days of 2023-11
WITH AllDays AS (
    SELECT 
        TRUNC(TO_DATE('2023-11-01', 'YYYY-MM-DD')) + LEVEL - 1 AS purchase_date
    FROM DUAL
    CONNECT BY LEVEL <= LAST_DAY(TO_DATE('2023-11-01', 'YYYY-MM-DD')) - TO_DATE('2023-11-01', 'YYYY-MM-DD') + 1
)

-- Filter Fridays
, Fridays AS (
    SELECT
        purchase_date,
        TRUNC((EXTRACT(DAY FROM purchase_date) - 1 + TO_NUMBER(TO_CHAR(purchase_date, 'D'))) / 7) AS week_of_month
        -- TO_CHAR(purchase_date, 'IW') AS week_of_month
    FROM AllDays
    WHERE TO_CHAR(purchase_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'FRI'
)

-- Calculate total amount spent on Fridays in November 2023
, t1 AS (
    SELECT
        p.*,
        EXTRACT(DAY FROM purchase_date) AS day_of_month,
        TO_CHAR(purchase_date, 'D') AS day_of_week,
        TRUNC((EXTRACT(DAY FROM purchase_date) - 1 + TO_NUMBER(TO_CHAR(purchase_date, 'D'))) / 7) AS week_of_month
        -- TO_CHAR(purchase_date, 'IW') AS week_of_month
    FROM
        Purchases p
    WHERE
        EXTRACT(MONTH FROM purchase_date) = 11
        AND EXTRACT(YEAR FROM purchase_date) = 2023
)

, t2 AS (
    SELECT
        week_of_month,
        purchase_date,
        SUM(NVL(amount_spend, 0)) AS total_amount
    FROM
        t1
    WHERE
        day_of_week = '6' -- Friday, depending on NLS_TERRITORY the first day of the week can vary, adjust accordingly
    GROUP BY
        week_of_month,
        purchase_date
)

-- Final selection to get all Fridays of November 2023
SELECT
    f.week_of_month,
    TO_CHAR(f.purchase_date, 'YYYY-MM-DD') as purchase_date,
    NVL(t2.total_amount, 0) AS total_amount
FROM
    Fridays f
LEFT JOIN
    t2 ON f.week_of_month = t2.week_of_month AND f.purchase_date = t2.purchase_date
ORDER BY
    f.purchase_date;