日別のデータを四半期(4月〜3月)でまとめるSQL

備忘録として。

日毎のデータテーブルを四半期毎にまとめる単純な方法です。

SELECT 
(CASE 
WHEN DATE_FORMAT(`date`,"%m") = "04" THEN "1Q" 
WHEN DATE_FORMAT(`date`,"%m") = "05" THEN "1Q" 
WHEN DATE_FORMAT(`date`,"%m") = "06" THEN "1Q" 
WHEN DATE_FORMAT(`date`,"%m") = "07" THEN "2Q" 
WHEN DATE_FORMAT(`date`,"%m") = "08" THEN "2Q" 
WHEN DATE_FORMAT(`date`,"%m") = "09" THEN "2Q" 
WHEN DATE_FORMAT(`date`,"%m") = "10" THEN "3Q" 
WHEN DATE_FORMAT(`date`,"%m") = "11" THEN "3Q" 
WHEN DATE_FORMAT(`date`,"%m") = "12" THEN "3Q" 
WHEN DATE_FORMAT(`date`,"%m") = "01" THEN "4Q" 
WHEN DATE_FORMAT(`date`,"%m") = "02" THEN "4Q" 
WHEN DATE_FORMAT(`date`,"%m") = "03" THEN "4Q" 
ELSE "non" END)as Q FROM `data_tabele` 
WHERE `date` BETWEEN "2018-04-01" AND "2019-03-31" 
GROUP BY Q