日曜から始まる並べ替えの方法

Question

SQLで曜日ごとの数値を取得し、日曜から始まるように並び替えたいです。

SELECT COUNT(page) AS pageviews,
FORMAT_DATE('%A',date_time) AS week
FROM sample.data
GROUP BY week

ORDER BY xxxxxxx
になると思うのですが、日曜から始まる並び替えのサンプルコードを見つけることが
できなく、こちらに来ました。

お手数ですが、考え方からご教示いただけますと幸いです。

よろしくお願い致します。

Comments

[Orlofsky]

Oracle Databaseの開発で汎用機のCOBOL屋さんが長かったメンバーがテーブル設計で _ アンダーバー ではなく、 - ハイフン を使いたがる人でテーブル名や列名をダブルクォーティングしないと使えないので困った事があります。

Answer 1

日曜から始まる並び替えのサンプルコードを
見つけることができなく、こちらに来ました。
お手数ですが、考え方からご教示いただけますと幸いです。

• FROM句にデータセットと思しき記述がある
• FORMAT_DATE関数を利用している
• GROUP BY 句に SELECT 句のエイリアスを記述している

以上の特徴から推測すると、ご利用のデータベースはGoogle BigQueryでしょうか。

で、sampleデータセット の dataテーブルに
_DATE型、或いはDATETIME型のカラムが存在しており、
日曜日を起点とした1週間 で グループ化された表 をSQLで作成したい
という解釈でいいでしょうか。

以下、コピペで即時実行できるサンプルを載せておきます。
(
共通式params内に記述されている日付リテラルを変更すると
結果セットも指定された範囲内のデータに置き換わります
)

WITH params AS 
(
    SELECT DATE '2021-09-01'  _start_  -- 変更可
         , DATE '2021-10-08'  _end_    -- 変更可
)
SELECT row_number() over() row
     , min( ymd )          date_from
     , max( ymd )          date_to
     , count(1)            days
     , array_to_string(
           [
               substring( '日月火水木金土', extract( dayofweek from min( ymd ) ), 1 )
             , substring( '日月火水木金土', extract( dayofweek from max( ymd ) ), 1 )
           ]
         , ' - '
       ) weekdays
     , date_add(
           date_sub( ymd, interval extract( dayofweek from ymd ) day )
         , interval 1 day
       ) group_from
     , date_add(
           date_sub( ymd, interval extract( dayofweek from ymd ) day )
         , interval 7 day
       ) group_to
FROM unnest(
         generate_date_array( 
             ( SELECT _start_ FROM params )
           , ( SELECT _end_   FROM params )
           , interval 1 day 
         )
) ymd
GROUP BY group_from
       , group_to
ORDER BY group_from ;

　
■結果

row	date_from	date_to	days	weekdays	group_from	group_to
1	2021/09/01	2021/09/04	4	水 - 土	2021/08/29	2021/09/04
2	2021/09/05	2021/09/11	7	日 - 土	2021/09/05	2021/09/11
3	2021/09/12	2021/09/18	7	日 - 土	2021/09/12	2021/09/18
4	2021/09/19	2021/09/25	7	日 - 土	2021/09/19	2021/09/25
5	2021/09/26	2021/10/02	7	日 - 土	2021/09/26	2021/10/02
6	2021/10/03	2021/10/08	6	日 - 金	2021/10/03	2021/10/09

追記

2021/08/23 08:13 に返信いただいたコメントから推理した
日付を横持ちにするだけのSQLです。

WITH params AS 
(
    SELECT DATE '2021-09-01'  _start_  -- 変更可
         , DATE '2021-10-08'  _end_    -- 変更可
)
SELECT row_number() over() row
     , max(
           case extract( dayofweek from ymd ) when 1 then ymd end
       ) sun
     , max(
           case extract( dayofweek from ymd ) when 2 then ymd end
       ) mon
     , max(
           case extract( dayofweek from ymd ) when 3 then ymd end
       ) tue
     , max(
           case extract( dayofweek from ymd ) when 4 then ymd end
       ) wed
     , max(
           case extract( dayofweek from ymd ) when 5 then ymd end
       ) thu
     , max(
           case extract( dayofweek from ymd ) when 6 then ymd end
       ) fri
     , max(
           case extract( dayofweek from ymd ) when 7 then ymd end
       ) sat
FROM unnest(
         generate_date_array( 
             ( SELECT _start_ FROM params )
           , ( SELECT _end_   FROM params )
           , interval 1 day 
         )
) ymd
GROUP BY date_add(
             date_sub( ymd, interval extract( dayofweek from ymd ) day )
           , interval 1 day
         ) 
       , date_add(
             date_sub( ymd, interval extract( dayofweek from ymd ) day )
           , interval 7 day
         )
ORDER BY min( ymd ) ;

　
■結果

row	sun	mon	tue	wed	thu	fri	sat
1				09/01	09/02	09/03	09/04
2	09/05	09/06	09/07	09/08	09/09	09/10	09/11
3	09/12	09/13	09/14	09/15	09/16	09/17	09/18
4	09/19	09/20	09/21	09/22	09/23	09/24	09/25
5	09/26	09/27	09/28	09/29	09/30	10/01	10/02
6	10/03	10/04	10/05	10/06	10/07	10/08

　
日曜日を起点( 区切り )としながらも、暦上の週数で行を分割する例はこちら。

WITH params AS 
(
    SELECT DATE '2021-09-01'  _start_  -- 変更可
         , DATE '2021-10-08'  _end_    -- 変更可
)
SELECT row_number() over() row
     , format_datetime( '%Y年%m月', min( ymd ) ) yymm
     , div( 
            extract( day from ymd ) - extract( dayofweek from ymd ) + 13
          , 7 
       ) wnum
     , max(
           case extract( dayofweek from ymd ) when 1 then ymd end
       ) sun
     , max(
           case extract( dayofweek from ymd ) when 2 then ymd end
       ) mon
     , max(
           case extract( dayofweek from ymd ) when 3 then ymd end
       ) tue
     , max(
           case extract( dayofweek from ymd ) when 4 then ymd end
       ) wed
     , max(
           case extract( dayofweek from ymd ) when 5 then ymd end
       ) thu
     , max(
           case extract( dayofweek from ymd ) when 6 then ymd end
       ) fri
     , max(
           case extract( dayofweek from ymd ) when 7 then ymd end
       ) sat
FROM unnest(
         generate_date_array( 
             ( SELECT _start_ FROM params )
           , ( SELECT _end_   FROM params )
           , interval 1 day 
         )
) ymd
GROUP BY date_trunc( ymd, month )
       , wnum
ORDER BY min( ymd ) ;

　
■結果

row	yymm	wnum	sun	mon	tue	wed	thu	fri	sat
1	2021年9月	1				09/01	09/02	09/03	09/04
2	2021年9月	2	09/05	09/06	09/07	09/08	09/09	09/10	09/11
3	2021年9月	3	09/12	09/13	09/14	09/15	09/16	09/17	09/18
4	2021年9月	4	09/19	09/20	09/21	09/22	09/23	09/24	09/25
5	2021年9月	5	09/26	09/27	09/28	09/29	09/30
6	2021年10月	1						10/01	10/02
7	2021年10月	2	10/03	10/04	10/05	10/06	10/07	10/08

Comments

[midori_saki]

ご回答ありがとうございます。
はい、BigQueryです。

私の伝え方がよくなかったようでして、各曜日（日曜日だけ、土曜日だけ、金曜日だけ、木曜日だけ、....)と曜日ごとに分け、日曜から始まるように列を並び替えたいという希望です。

申し訳ありませんが、再度ご教示いただけますでしょうか。

[mayu-]

特定範囲の日付１列のみで構成されている表から作成可能な
横持ちの結果セットといえば ... 「カレンダー」ということでしょうか。
推理した SQL と結果セットを回答に追記しておきます。

[midori_saki]

新しい回答を追加いただき、ありがとうございました。
大変お手数おかけしました。
いただいたコードを参考に、自分でも書いてみます！

Answer 2

SQLタグだけではどのデータベースかわかりません。MySQLでしたら、

DATE_FORMAT関数およびTIME_FORMAT関数関数の使い方 を熟読してSQLを実行しては？