/*売上テーブル(SALES)より、2024年3月1日から28日までの4週間分のデータを集計し、売上件数と売上金額合計(SALES_AMT)の一日当たりの平均値を曜日別に表示しなさい。

また、売上区分(SALES_TYPE) = 2 (売上返品)のデータ、および、赤黒伝票番号(UPDATED_NO)の値がNULL値以外(赤伝)のデータは集計対象外とする。

下記の項目を曜日ごとに集計して表示すること。(エイリアスを使用し→の項目名とする)

'日'からスタートして'金'、'土'までを表示 → WEEK
売上データの件数の1日平均件数を曜日別に集計(小数点以下は四捨五入) → AVG_CNT
売上金額合計の1日平均金額を曜日別に集計(小数点以下は四捨五入) → AVG_AMT
※金額は3桁ごとにカンマを入れ、最後に'円'を付けること。
ただし、使用されるデータの売上金額合計の値は最大9桁であることが保証される。
(printf関数の拡張機能は利用できないので注意すること。)

なお、同じ曜日の売上が1件もない場合は、その曜日は件数、金額とも0として表示すること。

表示順

曜日順('日'～'土')*/

WITH 
--日付リスト
DATE_TABLE AS
(SELECT
'2024-03-01' AS DAY
UNION
SELECT DATE(DAY,'+1 DAYS')
FROM DATE_TABLE
WHERE DAY < '2024-03-28'),

--曜日付加
DATE_WEEK_TABLE AS
(SELECT *,
STRFTIME('%w', DAY) AS NO,
case STRFTIME('%w', DAY)
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土'
    END AS WEEK
FROM DATE_TABLE),

--条件整理
WEEKLY AS 
(SELECT
SALES_DATE,
--STRFTIME('%w', SALES_DATE) AS NO,
COUNT(*) AS CNT,
SUM(SALES_AMT) AS AMT
FROM SALES
WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28' AND SALES_TYPE !=2 AND UPDATED_NO IS NULL
GROUP BY SALES_DATE)

--集計
,RSLT AS
(SELECT WEEK,
CAST(ROUND(AVG(IFNULL(CNT,0)))AS INTEGER) AS AVG_CNT, 
CAST(ROUND(AVG(IFNULL(AMT,0)))AS INTEGER) AS PRE_AVG_AMT
FROM DATE_WEEK_TABLE AS D
LEFT JOIN WEEKLY
ON DAY = SALES_DATE
GROUP BY WEEK
ORDER BY D.NO)

--カンマ区切り追加&まとめ
SELECT
WEEK,AVG_CNT,
CASE
WHEN LENGTH(PRE_AVG_AMT) > 6 THEN SUBSTR(PRE_AVG_AMT,1,LENGTH(PRE_AVG_AMT)-6)||','
ELSE ''
END ||
CASE
WHEN LENGTH(PRE_AVG_AMT) > 3 THEN SUBSTR(PRE_AVG_AMT,LENGTH(PRE_AVG_AMT)-5,3)||','||SUBSTR(PRE_AVG_AMT,LENGTH(PRE_AVG_AMT)-2,3)
ELSE PRE_AVG_AMT
END ||'円' AS AVG_AMT
FROM RSLT