【SQL】このコードの意味を教えて下さい ＋ 今週verを知りたいです

Question

SELECT Id, cvday
FROM Customer
WHERE date(replace(substr(cvday, 0, 11), '/', '-')) BETWEEN date('now', 'start of month')
AND date('now', 'start of month', '+1 month', '-1 day')

結果

0000 2021/11/02 12:00	
0001 2021/11/02 12:00	
0003 2021/11/01 12:00	
0004 2021/11/01 12:00

上記のコードは「先月、問い合わせした顧客」を取得しています。
cvdayが問い合わせをした日時が格納されています。
WHERE文で先月の文を抽出しているようなのですが、コードの意味が調べても理解できないです。
特に'+1 month', '-1 day'のあたりです。+1や-1はなぜ行っているのでしょうか？

また、**「今週問い合わせした顧客」**を抽出したいです。
例えば今日は11月1日(月)ならcvdayが11月1日(月)から11月7日(日)までの間のデータ抽出したいです。(静的ではく動的に日時を算出したいです。)

よろしくおねがいいたします。

Comments

[cx20]

SQL は DB によって方言があるので、何の DB かは記載した方が良いかと思います。
ググってみた感じだと SQLite の構文でしょうか？
https://www.tutlane.com/tutorial/sqlite/sqlite-date-function

[tarotarotarotar]

redashを使っております。
redashが何を使っているか正確にはわかりませんがmysqlだと思われます。

[cx20]

> redashを使っております。

redash を使ったことは無いのですが、SQLite の構文で SQL を書く必要があるようですね。

■ Redash Query Results の使い方 - 注意：普段使っている関数や演算子が使えないことがある
https://zenn.dev/shoezawa/articles/0e72d2aa061262#%E6%B3%A8%E6%84%8F%EF%BC%9A%E6%99%AE%E6%AE%B5%E4%BD%BF%E3%81%A3%E3%81%A6%E3%81%84%E3%82%8B%E9%96%A2%E6%95%B0%E3%82%84%E6%BC%94%E7%AE%97%E5%AD%90%E3%81%8C%E4%BD%BF%E3%81%88%E3%81%AA%E3%81%84%E3%81%93%E3%81%A8%E3%81%8C%E3%81%82%E3%82%8B

> Redash の Query Results は SQLite ベースで実装されているので、普段 MySQL や PostgreSQL を使っていたとしてもデータソースを Query Results にすると強制的に SQLite で書かないといけなくなります。

[tarotarotarotar]

ありがとうございます。

なるほど、初めて知りました。
確かに使っていて、よきせぬ挙動をするときがあるので何を使っているのか明確にはしらなかったです。

SQLiteは全く使ったこと無いので苦戦してます...

Answer 1

質問頂いたSQLをちょっとわかりやすく書くと以下のようになります。

SELECT Id, cvday
FROM Customer
WHERE date(replace(substr(cvday, 0, 11), '/', '-')) 
BETWEEN date('now', 'start of month')AND date('now', 'start of month', '+1 month', '-1 day')

BETWEEN date('now', 'start of month')AND date('now', 'start of month', '+1 month', '-1 day')の部分だけの意味は今月のはじめから今月末までのデータを絞り込む条件ということになります。なので、date('now', 'start of month', '+1 month', '-1 day')の関数で使っている値が出てくる順に言うと今月の頭の日付から1ヶ月進んでそこから1日戻すということになります。そうすることで今月末の日付を指定することができるようになるわけです。修飾子の中で月末を指定することができればいいのですが、SQLの修飾子にないので無理やりこういう記述になっています。

また、これの今週バージョンであればこのBETWEENの箇所をいじると、

BETWEEN date('now', '-6 days')AND date('now')

になるかと思います。

日付周りはこのサイトで説明されているので、分からなければ一度勉強するといいかと思います。

https://www.dbonline.jp/sqlite/function/index6.html#section2

Comments

[tarotarotarotar]

ありがとうございます！
非常にわかりやすいご説明で理解することができました。

また、今週のコードもありがとうございます。
そのままだと動かなかったのですが
BETWEEN date('now', '-6 day')AND date('now')
とすることで取得することができました。

非常に感謝です。