【LeetCode2993. 发生在周五的交易 I】MySQL，T-SQL，Oracle里尝试实现weekofmonth功能

目录

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

题目地址

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

说明

---

对于计算一个日期是该月的第几周，通常有两种常见的范式：

1. ISO-8601 Week Numbering:

   • 在这个标准中，每周从周一开始。
   • 第一周是包含该年第一个周四的周。
   • 这意味着如果1月1日是周五、周六或周日，那么1月1日将属于上一年的最后一周。

2. North American System (或者其他以周日开始的系统):

   • 在这个系统中，每周从周日开始。
   • 第一周是从1月1日开始的那个周。

代码

这里只是一个简单的算法，用于根据月份的第一天是星期几来估算日期是该月的第几周。这种计算的逻辑是：

• 如果月份的第一天是周日，那么我们可以直接将日期的天数除以7。
• 如果月份的第一天是其他周几，我们需要根据这个偏移量进行调整。

MySQL

# Write your MySQL query statement below

with 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(amount_spend) 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
)

select * from t2

T-SQL

WITH t1 AS (
    SELECT
        *,
        DAY(purchase_date) as day_of_month,
        DATEPART(weekday, purchase_date) as day_of_week,
        (DATEPART(day, purchase_date) + DATEPART(weekday, DATEFROMPARTS(YEAR(purchase_date), MONTH(purchase_date), 1)) - 2) / 7 + 1 as week_of_month
    FROM
        Purchases
),
t2 AS (
    SELECT
        week_of_month,
        purchase_date,
        SUM(amount_spend) as total_amount
    FROM t1
    WHERE day_of_week = ((5 + @@DATEFIRST) % 7) + 1 -- Dynamic calculation for Friday
      AND FORMAT(purchase_date, 'yyyy-MM') = '2023-11'
    GROUP BY week_of_month, purchase_date
)

SELECT * FROM t2
ORDER BY week_of_month ASC;

Oracle

WITH 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
    FROM
        Purchases p
),
t2 AS (
    SELECT
        week_of_month,
        purchase_date,
        SUM(amount_spend) AS total_amount
    FROM t1
    WHERE TO_CHAR(purchase_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'FRI' -- Independent of NLS_TERRITORY
      AND EXTRACT(YEAR FROM purchase_date) = 2023
      AND EXTRACT(MONTH FROM purchase_date) = 11
    GROUP BY week_of_month, purchase_date
)

SELECT
    week_of_month,
    TO_CHAR(purchase_date, 'YYYY-MM-DD') as purchase_date,
    total_amount
FROM t2
ORDER BY week_of_month ASC;