【PostgreSQL】特定の期間・曜日・時間のデータを抽出したい

Question

##前提
PostgreSQLを使用しており、以下のようなDBがあります。

【hoge_db】

no	active_time
1	202104011200
2	202104011300
3	202104031900
4	202104051600

##やりたいこと
上記のactive_timeのデータを使って、特定の期間の、特定の曜日の、特定の時間のデータを抽出し、件数をカウントしたいです。

たとえば、直近１か月の日曜日の12時～18時のデータは何件ある、という感じです。
（2021/3/21 12-18が2件、2021/3/28 12-18が3件、というイメージ）

自分なりに調べたのですが、混乱してしまい…どうかお助けください。
・active_time BETWEEN '12:00:00' AND '17:59:59'で時間帯を指定する
・TO_CHAR(active_time, 'YYYY/DD/MM hh:MI:SS Dy')で曜日を表示する
・COUNT～GROUP BYで集計する

SQL一文でまとめたいのですが、どのように書けばいいでしょうか。

Comments

[sazi]

active_timeの型は何ですか？

[退会済みユーザー]

stringです。
省略して書いたのですが、実際にはactive_timeというカラムはなく、長い文字列からTO_CHAR(CAST(SUBSTR(***),as timestamp), 'YYYYMMDDhhMISS') AS active_timeで出した結果になります。
別名カラムは式に直接使えないらしいので、「active_time」と記述したところはすべて上記の式を代入する形になります。

[sazi]

元になっている項目は文字型という事でしょうか？
その文字型に格納されている、文字列はどのような書式でしょうか。
混乱しているのは、active_timeへの編集も関係している感じですね。

[退会済みユーザー]

元の文字列は「2021040112000012345678」のような22桁の文字列です。
日付時間と細かい秒数なんだと思うのですが…
型は「character varying(22)」とありました。

[sazi]

20桁までは、’YYYYMMDDHH24MISSUS’という書式だと推測しますが、残りの2桁が不明です。
実際の値はどのようなものですか？

Answer 1

（2021/3/21 12-18が2件、2021/3/28 12-18が3件、というイメージ）

上記を出力のイメージとし、

特定の期間の、特定の曜日の、特定の時間のデータを抽出し

上記を抽出条件とした場合のSQLを考えてみます。

別名カラムは式に直接使えないらしいので

ネストさせれば使えます。
可読性の為に、先ずは目的に応じた編集を先に行い、次に条件や集計を行うようにしています。

ここでは時間帯を６時間ごとのグループにする編集を行っています。

取り敢えず条件部分はわざとコメントにしていますので、適宜利用して下さい。

select active_date, active_day, active_timezone, count(*) as cnt
from (
  select *
       , to_date(substr(active, 1, 8), 'YYYYMMDDHH')                  AS active_date    -- 日付
       , to_char(to_date(substr(active, 1, 8), 'YYYYMMDDHH'), 'TMDy') AS active_day     -- 曜日
       , to_char(substr(active, 9, 6)::integer,'FM00:00:00')::time    AS active_time    -- 時間
       , case substr(active, 9, 2)::integer / 6 
           when 0 then '00-06'
           when 1 then '06-12'
           when 2 then '12-18'
           when 3 then '18-24'
         end                                                          AS active_timezone -- 時間帯
       , to_timestamp(substr(active, 1, 20), 'YYYYMMDDHH24MISSUS')    AS active_timestamp -- タイムスタンプ
  from (values
         (1,'2021040112000000000000')
        ,(2,'2021040113000000000000')
        ,(3,'2021040319000000000000')
        ,(4,'2021040516000000000000')
       ) as hoge_db(no, active)
) edit
/*
where active_date between '2021/04/01' and '2021/04/05'
  and active_day in ('月','木','土')
  and active_time between '12:00:00' AND '17:59:59'
*/
group by active_date, active_day, active_timezone 
order by active_date, active_timezone 

元の文字列は「2021040112000012345678」のような22桁の文字列です。

という事なので、桁数的に不明な部分がありますので、２０桁が’YYYYMMDDHH24MISSUS’だとして、必要な桁のみをsubstrでピックアップする事にしています。

Answer 2

期間や曜日や時間での絞り込みクエリーがよくある操作なのだとしたら、
いちいちvarcharとかtextとかから関数かまして加工した上での絞り込みって
レコード件数次第ではメモリに上がりきらなくなって極端に遅くなる場合も考えられそう。

式に対するインデックスを組み合わせるのはもちろんだけど、

• 期間の絞り込み用にtimestamp型で持つ、
• 曜日の絞り込み用に曜日カラムを持つ、
• 時間の絞り込み用に時成分のみのintegerカラムを持つ、

って工夫をしたらいいと思った。
そういうviewを予め組んでもいいだろうし、
データ投入時点でpl/pgSQLを駆使して検索用に加工したものが
自動反映されるようにするなどの工夫もありかもしれない。

select 
  substring(t.active_time from 1 for 4) as yyyy,
  substring(t.active_time from 5 for 2) as mm,
  substring(t.active_time from 7 for 2) as dd,
  substring(t.active_time from 9 for 2) as hh,
  substring(t.active_time from 11 for 2) as nn
from (
  select '202104011200' as active_time
) as t

これで年月日時分が分離できるし、

select 
  to_timestamp(substring(t.active_time from 1 to 14), 'YYYYMMDDHH24MISS') as "タイムスタンプ",
  extract(dow from to_timestamp(substring(t.active_time from 1 to 14), 'YYYYMMDDHH24MISS')) as "dow(0が日曜日)",
  extract(hour from to_timestamp(substring(t.active_time from 1 to 14), 'YYYYMMDDHH24MISS')) as "hour"
from (
  select '202104011200' as active_time
) as t

これでtimestamp型にできるからここから加工すればいいんじゃないかと。

Comments

[退会済みユーザー]

回答ありがとうございます。
確かに、あらかじめカラムやviewを持つのがいいとは思うんですが…今回は突発的な調査なのと、DBは実際に会社で運用しているものなので、編集は禁止されています。（SELECTでの読み取りのみ可能）

[退会済みユーザー]

追記コメント内の22文字ってのに合わせてクエリーを直してみたり。
こんな感じで加工はできるので、それを踏まえて組み立てたらって話だったけど、さすがsaziさんだわ。