WEEK関数とSUBSTRING関数：週番号から年と週番号を抽出する

MariaDBにおけるWEEK関数：詳細解説と実践的な例

概要:

• WEEK関数は、以下の2つの形式で使用できます。

# 形式1: 日付のみ
WEEK(date_column)

# 形式2: 日付と開始曜日
WEEK(date_column, start_day)

• 返される週番号は、ISO 8601規格に基づいています。
• デフォルトの開始曜日は月曜日です。
• オプションで開始曜日を日曜日から土曜日まで指定できます。

詳細:

• 形式1: 日付のみを指定する場合、WEEK関数はその日付を含む週番号を返します。週番号は、1月1日が含まれる週を1週目とし、年を跨いでカウントされます。

SELECT WEEK('2024-03-21');
# 結果: 12

• 形式2: 日付と開始曜日を指定する場合、WEEK関数はその日付を含む週番号を、指定された開始曜日を基準に返します。

SELECT WEEK('2024-03-21', 1); # 開始曜日: 日曜日
# 結果: 13

SELECT WEEK('2024-03-21', 7); # 開始曜日: 土曜日
# 結果: 11

実践的な例:

• 特定の週に含まれるすべての注文を抽出する

SELECT *
FROM orders
WHERE WEEK(order_date) = 12;

• 過去1年間の売上を週ごとに集計する

SELECT WEEK(order_date) AS week, SUM(order_total) AS total_sales
FROM orders
WHERE order_date BETWEEN '2023-03-21' AND '2024-03-20'
GROUP BY week;

• 特定の曜日に開始する週のデータのみを抽出する

SELECT *
FROM orders
WHERE WEEK(order_date, 2) = 12; # 開始曜日: 月曜日

補足:

• WEEK関数は、YEAR関数と組み合わせて、特定の年の特定の週に関連するデータを抽出するのに役立ちます。

SELECT *
FROM orders
WHERE YEAR(order_date) = 2024
AND WEEK(order_date) = 12;

• WEEK関数は、DATE_FORMAT関数と組み合わせて、週番号をより分かりやすい形式で表示するのに役立ちます。

SELECT DATE_FORMAT(order_date, '%Y-%W') AS week, SUM(order_total) AS total_sales
FROM orders
WHERE order_date BETWEEN '2023-03-21' AND '2024-03-20'
GROUP BY week;

# 結果:
# week | total_sales
#-------|------------
# 2023-12 | 1000
# 2024-01 | 1200
# ...

注意:

• 上記の例は、あくまでも参考です。実際の使用例は、要件に合わせて調整する必要があります。

WEEK関数を使ったサンプルコード集

特定の週に含まれる日付をすべて表示する

SELECT date_column
FROM your_table
WHERE WEEK(date_column) = 12; # 12週目の日付を抽出

特定の年の特定の週に含まれるデータを抽出する

SELECT *
FROM your_table
WHERE YEAR(date_column) = 2024
AND WEEK(date_column) = 12; # 2024年12週目のデータを抽出

過去1年間の売上を週ごとに集計する

SELECT WEEK(order_date) AS week, SUM(order_total) AS total_sales
FROM orders
WHERE order_date BETWEEN '2023-03-21' AND '2024-03-20'
GROUP BY week;

特定の曜日に開始する週のデータのみを抽出する

SELECT *
FROM orders
WHERE WEEK(order_date, 2) = 12; # 月曜日から始まる12週目のデータのみ抽出

週番号と売上をわかりやすい形式で表示する

SELECT DATE_FORMAT(order_date, '%Y-%W') AS week, SUM(order_total) AS total_sales
FROM orders
WHERE order_date BETWEEN '2023-03-21' AND '2024-03-20'
GROUP BY week;

# 結果:
# week | total_sales
#-------|------------
# 2023-12 | 1000
# 2024-01 | 1200
# ...

複数のWEEK関数を組み合わせて、より複雑な条件を設定する

SELECT *
FROM orders
WHERE WEEK(order_date, 2) = 12
AND WEEK(order_date, 7) > 20; # 月曜日から始まる12週目で、土曜日を含む週のみ抽出

WEEK関数をCASE式と組み合わせて、週番号に基づいて異なる値を返す

SELECT CASE WEEK(order_date)
    WHEN 1 THEN 'Q1'
    WHEN 13 THEN 'Q2'
    WHEN 26 THEN 'Q3'
    ELSE 'Q4'
END AS quarter, SUM(order_total) AS total_sales
FROM orders
GROUP BY quarter;

# 結果:
# quarter | total_sales
#-------|------------
# Q1     | 1000
# Q2     | 1200
# Q3     | 1500
# Q4     | 1800

WEEK関数をSUBSTRING関数と組み合わせて、週番号から年と週番号を抽出する

SELECT SUBSTRING(DATE_FORMAT(order_date, '%Y-%W'), 1, 4) AS year,
       SUBSTRING(DATE_FORMAT(order_date, '%Y-%W'), 6, 2) AS week
FROM orders;

# 結果:
# year | week
#-------|------------
# 2023 | 12
# 2023 | 13
# 2023 | 14
# ...

WEEK関数以外の方法

DATE_FORMAT関数とEXTRACT関数を組み合わせて、週番号を取得することができます。

SELECT DATE_FORMAT(order_date, '%Y-%W') AS week, SUM(order_total) AS total_sales
FROM orders
GROUP BY week;

# 結果:
# week | total_sales
#-------|------------
# 2023-12 | 1000
# 2024-01 | 1200
# ...

SELECT EXTRACT(WEEK FROM order_date) AS week, SUM(order_total) AS total_sales
FROM orders
GROUP BY week;

# 結果:
# week | total_sales
#-------|------------
# 12 | 1000
# 1 | 1200
# ...

YEARWEEK関数:

MySQL 8.0以降では、YEARWEEK関数を使用することができます。YEARWEEK関数は、指定された日付を含む週番号と年を返します。

SELECT YEARWEEK(order_date) AS yearweek, SUM(order_total) AS total_sales
FROM orders
GROUP BY yearweek;

# 結果:
# yearweek | total_sales
#-------|------------
# 202352 | 1000
# 202401 | 1200
# ...

CASE式を使用して、特定の条件に基づいて週番号を抽出することができます。

SELECT CASE WHEN MONTH(order_date) IN (1, 2, 3) THEN 'Q1'
        WHEN MONTH(order_date) IN (4, 5, 6) THEN 'Q2'
        WHEN MONTH(order_date) IN (7, 8, 9) THEN 'Q3'
        ELSE 'Q4'
    END AS quarter, SUM(order_total) AS total_sales
FROM orders
GROUP BY quarter;

# 結果:
# quarter | total_sales
#-------|------------
# Q1     | 1000
# Q2     | 1200
# Q3     | 1500
# Q4     | 1800

自作関数:

上記の方法で