WITH SUB AS (SELECT SALES_DATE,
SUBSTR('日月火水木金土', STRFTIME('%w', SALES_DATE) + 1, 1) AS WEEK,
SALES_AMT
FROM SALES
WHERE SALES_TYPE <> 2
AND UPDATED_NO IS NULL
AND SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'),
SUB2 AS (SELECT WEEK,
ROUND(1.0 * COUNT(*) / 4) AS AVG_CNT,
ROUND(1.0 * SUM(SALES_AMT) / 4) AS AVG_AMT
FROM SUB
GROUP BY WEEK),
WEEK_TBL AS (SELECT
'2024-03-01' AS CLDATE,
STRFTIME('%w', '2024-03-01') AS WEEK_NO,
SUBSTR('日月火水木金土', STRFTIME('%w', '2024-03-01') + 1, 1) AS WEEK
UNION ALL
SELECT DATE(CLDATE, '+1 days'),
STRFTIME('%w', DATE(CLDATE, '+1 days')),
SUBSTR('日月火水木金土', STRFTIME('%w', DATE(CLDATE, '+1 days')) + 1, 1)
FROM WEEK_TBL
WHERE CLDATE < '2024-03-07'),
SUB3 AS (SELECT WEEK_TBL.WEEK,
COALESCE(AVG_CNT, 0) AS AVG_CNT,
COALESCE(AVG_AMT, 0) AS AVG_SA_AMT
FROM WEEK_TBL
LEFT JOIN SUB2
ON WEEK_TBL.WEEK = SUB2.WEEK
ORDER BY WEEK_NO)
SELECT WEEK,
AVG_CNT,
CASE WHEN LENGTH(AVG_SA_AMT) > 6
THEN SUBSTR(AVG_SA_AMT, 1, LENGTH(AVG_SA_AMT) - 6) || ',' || SUBSTR(AVG_SA_AMT, LENGTH(AVG_SA_AMT) - 5, 3) || ',' || SUBSTR(AVG_SA_AMT, LENGTH(AVG_SA_AMT) - 2, 3)
WHEN LENGTH(AVG_SA_AMT) > 3
THEN SUBSTR(AVG_SA_AMT, 1, LENGTH(AVG_SA_AMT) - 3) || ',' || SUBSTR(AVG_SA_AMT, LENGTH(AVG_SA_AMT) - 2, 3)
ELSE AVG_SA_AMT END || '円'
AS AVG_AMT
FROM SUB3