「投稿ID（Int型）」「投稿したユーザーID（Int型）」「投稿日時（String型）」が入ったPostテーブルにおいて、

id	user_id	created_at
1	10	2019-11-01T22:11:05.000+09:00
2	10	2019-11-02T22:11:05.000+09:00
3	20	2019-11-01T22:11:05.000+09:00

下記のように、投稿ユーザーごとに11/1~11/3それぞれで投稿したかどうかを判定する出力を出したいです。（投稿した日は「true」、投稿していない日は「false」）
|user_id|11/1|11/2|11/3|
|:--|:--:|--:|
|10|true|true|false|
|20|true|false|false|

その際に、下記のようなクエリを作成したのですが、投稿日時もGROUP BYしてしまっているため、1ユーザーに対して3行出力されてしまっているが現状です。

SQL
1SELECT DISTINCT
2    user_id,
3    CASE
4        WHEN SUBSTRING(created_at, 1, 10) = '2019-11-01' THEN 'true'
5        ELSE 'false'
6    END AS one_day,
7    CASE
8        WHEN SUBSTRING(created_at, 1, 10) = '2019-11-02' THEN 'true'
9        ELSE 'false'
10    END AS two_day,
11    CASE
12        WHEN SUBSTRING(created_at, 1, 10) = '2019-11-03' THEN 'true'
13        ELSE 'false'
14    END AS three_day
15FROM
16    posts
17WHERE
18    DATE(SUBSTRING(created_at,1,10)) BETWEEN DATE('2019-11-01') AND DATE('2019-11-03') 
19GROUP BY
20    user_id, SUBSTRING(created_at, 1, 10)

GROUP BYを投稿ユーザーのみにしてしまうと、下記のようなエラーメッセージが出てしまい、ここからどのようにクエリを書き換えれば良いか困っています。実行エンジンはprestoです。
ご教示のほど、何卒よろしくお願いいたします！mm

'created_at' must be an aggregate expression or appear in GROUP BY clause

yambejp

2019/11/20 10:48 編集

タグ付はされているようですが、念の為動作環境をお書きください created_atは日時型ではなく文字列型なのでしょうか？

Orlofsky

2019/11/20 11:07

SQLで使っているテーブルのCREATE TABLEも質問に提示できた方が適切なコメントが付き易いことに気がつけると良いのですが。

Shinog

2019/11/20 14:31

フィードバックいただき、誠にありがとうございます！ >動作環境実行エンジンはprestoとなっております。 >created_atは日時型ではなく文字列型なのかはい、文字列型です。

行動規範の内容に同意します

回答2件

SQL
1DATE(SUBSTRING(created_at,1,10)) BETWEEN DATE('2019-11-01') AND DATE('2019-11-03')

上記条件を入れると全てfalseのデータは抽出されませんよ？
必要なら以下のインラインビュー内に追記して下さい。

SQL
1SELECT user_id
2     , exists(
3         select 1 from posts 
4         where user_id=t.user_id and SUBSTRING(created_at, 1, 10)='2019-11-01'
5       ) one_day
6     , exists(
7         select 1 from posts 
8         where user_id=t.user_id and SUBSTRING(created_at, 1, 10)='2019-11-02'
9       ) two_day
10     , exists(
11         select 1 from posts 
12         where user_id=t.user_id and SUBSTRING(created_at, 1, 10)='2019-11-03'
13       ) three_day
14from (select user_id	FROM posts GROUP BY user_id) t

投稿2019/11/21 06:36

sazi

総合スコア25327

一応こんな感じでいけるかもしれません

元データ

SQL
1create table posts(id int,user_id int,created_at varchar(30));
2insert into posts values
3(1,10,'2019-11-01T22:11:05.000+09:00'),
4(2,10,'2019-11-02T22:11:05.000+09:00'),
5(3,20,'2019-11-01T22:11:05.000+09:00');

検索

SQL
1SELECT 
2    user_id,
3    SUM(CASE SUBSTRING(created_at, 1, 10)
4        WHEN  '2019-11-01' THEN 1
5        ELSE 0
6    END) AS one_day,
7    SUM(CASE SUBSTRING(created_at, 1, 10)
8        WHEN  '2019-11-02' THEN 1
9        ELSE 0
10    END) AS two_day,
11    SUM(CASE SUBSTRING(created_at, 1, 10)
12        WHEN  '2019-11-03' THEN 1
13        ELSE 0
14    END) AS three_day
15FROM
16    posts
17WHERE
18    DATE(SUBSTRING(created_at,1,10)) BETWEEN DATE('2019-11-01') AND DATE('2019-11-03') 
19GROUP BY user_id

結果

user_id	one_day	two_day	three_day
10	1	1	0
20	1	0	0

true/false

カウントしていた結果を0より大きいかどうかで判断してはどうでしょうか？

SQL
1SELECT 
2    user_id,
3    SUM(CASE SUBSTRING(created_at, 1, 10)
4        WHEN  '2019-11-01' THEN 1
5        ELSE 0
6    END)>0 AS one_day,
7    SUM(CASE SUBSTRING(created_at, 1, 10)
8        WHEN  '2019-11-02' THEN 1
9        ELSE 0
10    END)>0 AS two_day,
11    SUM(CASE SUBSTRING(created_at, 1, 10)
12        WHEN  '2019-11-03' THEN 1
13        ELSE 0
14    END)>0 AS three_day
15FROM
16    posts
17WHERE
18    DATE(SUBSTRING(created_at,1,10)) BETWEEN DATE('2019-11-01') AND DATE('2019-11-03') 
19GROUP BY user_id

投稿2019/11/20 10:56

編集2019/11/21 00:42

yambejp

総合スコア116661

Shinog

2019/11/20 14:34

こちら、大変感謝いたします！無事user_idごとに1行出力されるようにはなったのですが、各日の「合計投稿数」というよりも「投稿したか否か」の情報が欲しく、、！mm 誤解を招くような質問になってしまい、申し訳ありません。「0と1」ではなく、「trueとfalse」に質問文も変更させていただきました！