日付、ユーザごとに、特定アイテムをカウントしたい

Question

SQLのことで教えていただけませんか。

user_tblに、そのユーザが最も好きなアイテム(top1),2番目に好きなアイテム(top2)があります。
shopping_tblに、その日にユーザが得たアイテムが、日付順で1つずつ並んでいます。

user_tbl
|id|date|top_1|top_2|
|01|0101|item_a|item_b|
|01|0102|item_a|item_b|
・
・
・
|99|1231|item_c|item_b|

shopping_tbl
|id|date|item|
|01|0101|item_a|
|01|0101|item_a|
|01|0101|item_c|
|01|0101|item_b|
・
・
・
|99|1231|item_c|

上記から、各ユーザがその日に得たアイテムのうちtop1,top2に該当するものを集計してcount_tblを作りたいです。

count_tbl
|id|date|top1_count|top2_count|
|01|0101|2|1|
|01|0102|3|0|
・
・
・
|99|1231|4|0|

どなたか、お知恵を貸していただけないでしょうか。
最終的にはHiveに翻訳する予定ですが、SQLで教えていただければと思います。

いくつかクエリを流していますが、COUNT(item) OVER(PARTITION BY id, item)でうまくいかず、LEFT OUTER JOINではすべての列を出すことができなくて悩んでいます。

Comments

[errormaker74]

データベースはなんでしょうか？(Oracle？PostgreSQL？など)
あとたいていのデータベースではdateは予約語なのでカラム名には使わないほうがよいでしょう。

一例ですが、下記のようなSELECT文で取れませんか？(Oracleを想定して書きました。dateは予約語なのでダブルクォーテーションで囲ってます)
INSERT INTO SELECTのような方法でテーブルに挿入可能だと思います。
```
SELECT
ut.id
,ut."date"
,(SELECT COUNT(st.item) FROM shopping_tbl st WHERE st.id = ut.id AND st."date" = ut."date" AND st.item = ut.top_1) as top1_count
,(SELECT COUNT(st.item) FROM shopping_tbl st WHERE st.id = ut.id AND st."date" = ut."date" AND st.item = ut.top_2) as top2_count
FROM
user_tbl ut
```

[Satomi07]

ありがとうございます。
データベースはHiveですが、ヒントを頂いてHiveに翻訳しようかと思っていました。

[Orlofsky]

HiveQLは[SQL]タグを削った方が良いみたいです。
http://chopl.in/post/2013/12/04/bad_hive_queries/

[Satomi07]

ありがとうございます、ただ、構成のヒントなどは使えますし、同じ部分もあります。
なによりHiveだけで回答をもらうより、多く知恵を頂けるので…。

[errormaker74]

私も[SQL]のタグを削った方が良いと思います。
SQLにも方言や独特の機能がありますし、その知識を持って答えたとしてもHiveで使えないなら教える側の時間の無駄だと思います。

[Satomi07]

以前、質問して得たSQL文を、自分でhiveに翻訳してうまく行きました。コピーするつもりではないです。SQLも、まだ書きなれていないため、私には考え方がとても有益です。明日これで試してみます。

[Orlofsky]

せっかくURLを提示したのに、読んでないか、読んでも理解できないようで。

[Satomi07]

すみません、一応両方の環境を自分で作って試しておりますし、自分でも調べてから質問していますから、このサイトもみました。

だから、コピーするためだけでない、翻訳すると書きました。

私は、クエリの構成法を知りたいのです。

Answer 1

追記依頼を見たらHiveではなくても良さそうだったので、MySQLで回答しますが、
副問い合わせと条件付きcountでできると思います。

select query.id, query.date, count(query.item = top_1 or null) as top1_count, count(query.item = top_2 or null) as top2_count from (
select u.*, s.id as s_id, s.date as s_date, s.item from user_tbl u
left outer join shopping_tbl s on u.id = s.id and u.date = s.date and (u.top_1 = s.item or u.top_2 = s.item)
) query
group by query.id, query.date;

テストデータ

-- DDL
CREATE TABLE `shopping_tbl` (
  `id` int NOT NULL,
  `date` varchar(4) DEFAULT NULL,
  `item` varchar(45) DEFAULT NULL
);

CREATE TABLE `user_tbl` (
  `id` int NOT NULL,
  `date` varchar(4) DEFAULT NULL,
  `top_1` varchar(45) DEFAULT NULL,
  `top_2` varchar(45) DEFAULT NULL
);

-- データ
INSERT INTO user_tbl VALUES (1,'0101','item_a','item_b');
INSERT INTO user_tbl VALUES (1,'0102','item_a','item_b');
INSERT INTO user_tbl VALUES (1,'0103','item_c','item_b');
INSERT INTO user_tbl VALUES (99,'1231','item_c','item_b');
INSERT INTO shopping_tbl VALUES (1,'0101','item_a');
INSERT INTO shopping_tbl VALUES (1,'0101','item_a');
INSERT INTO shopping_tbl VALUES (1,'0101','item_c');
INSERT INTO shopping_tbl VALUES (1,'0101','item_b');
INSERT INTO shopping_tbl VALUES (1,'0102','item_a');
INSERT INTO shopping_tbl VALUES (1,'0102','item_a');
INSERT INTO shopping_tbl VALUES (1,'0102','item_a');
INSERT INTO shopping_tbl VALUES (1,'0103','item_a');
INSERT INTO shopping_tbl VALUES (1,'0103','item_b');
INSERT INTO shopping_tbl VALUES (1,'0103','item_a');
INSERT INTO shopping_tbl VALUES (99,'1231','item_c');

結果

id	date	top1_count	top2_count
1	0101	2	1
1	0102	3	0
1	0103	0	1
99	1231	1	0

Comments

[Satomi07]

SochiAdachi様、ご回答ありがとうございます。
left outer joinならHiveで行けます、大変助かりました。

教えていただいたクエリを基にHiveに翻訳して、下記で回すことができました。
SELECT
user_tbl.id,
user_tbl.date_,
SUM(IF(user_tbl.top_1=shopping_tbl.item,1,0)) AS top1_count,
SUM(IF(user_tbl.top_2=shopping_tbl.item,1,0)) AS top2_count
FROM
user_tbl
LEFT OUTER JOIN shopping_tbl
ON(user_tbl.id=shopping_tbl.id AND user_tbl.date_=shopping_tbl.date_)
GROUP BY user_tbl.id,user_tbl.date_

errormaker74様、Orlofsky様にも感謝を。