SQLのクエリの書き方を教えて下さい

◆私のスキル

主に営業を担当してて、
データ抽出作業はマニュアルを見ながらやっとできるレベルです。
HIVEQLというのを使っています

◆状況
一日単位の会員の閲覧履歴が入ったテーブルがあります。

会員ID（文字）　閲覧日時（文字yyyy/mm/dd hh:mm:ss）　閲覧コンテンツID（文字）　パーティション（閲覧日　文字　yy-mm-dd)
Aさん　2016/02/12 09:21:31 000233033 2016-02-12
Bさん　2016/02/12 09:25:35 000233655 2016-02-12
Aさん　2016/02/12 09:27:31 000233076 2016-02-12
Cさん　2016/02/12 09:29:37 000558907 2016-02-12

◆やりたいこと
（１）コンテンツIDごとに2週間単位で閲覧者（重複なし）の数の数える
（２）コンテンツIDごとに1ヶ月単位で閲覧者（重複なし）の数の数える

（1）
10月2日〜10月15日　10月16日〜10月29日　10月30日〜11月13日　・・・
コンテンツA 240　230　190

（2）　
10月2日〜11月1日　11月2日〜12月1日　12月2日〜1月1日　・・・
コンテンツA 400　300　310

のような形式で考えています。

◆条件
・コンテンツごとに初回の開始日が異なっていています。
・コンテンツの種類が12種類あります
・データは約2年分あります

◆2週ごとの合計は
--
SELECT
count (distinct 会員ID)
FROM
テーブル
where
閲覧コンテンツID　＝〇〇
パーティション　between '2017/02/12' and '2017/02/25'
--
これを2週間毎に繰り返す

1ヶ月ごともパーティションを変えて同様に出すことはできます。


●
・コンテンツ単位で2週間ごとに「まとめて」出力したい
・コンテンツ単位で1ヶ月ごとに「まとめて」出力したい

・できればSQLのクエリを少し変えて使いまわしたいです。

やり方をご存じの方がいらっしゃったら
教えて下さい

・代替案として
1ヶ月単位として
2016年1月1日〜1月31日　2月1日〜2月28日　3月1日〜3月31日　・・・

同様に1週間単位として
日曜から土曜までを一つの単位として
まとめればかんたんにだせる

のようなアイデアもあれば教えて下さい

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

No.2 ベストアンサー 回答者： kmee 回答日時： 2019/12/11 08:25

HIVESQL自体は使ったことないのですが。

横に並べるのは必須なのでしょうか?

SQLで「縦に並んだデータを横にして集計する」というのは結構面倒です。

閲覧コンテンツID, 集計開始日, ユーザー数
--------------------------------
コンテンツA,10月2日, 240
コンテンツA,10月16日,230
コンテンツA,10月30日,190
....
のように「縦に並んだ」データを取得して、それを別のツールで「横に並べる」のが楽です。
※ 例えば、 上記の「縦」のCSVをExcelのピボットテーブルで「横」にする、とか

https://qiita.com/k24d/items/79bc4828c918dfeeac34
https://oshiete.goo.ne.jp/qa/11381504.html
横に並び変える方法はいくつかあるのですが「使う列を列挙する」必要があります。

例えば、3ヶ月分のSQLから6ヶ月分のSQLにしたら、追加の3ヶ月分の列を記述する必要があります。
2018年用のSQLから2019年用を作ろうとすると、列の分だけ全て修正する必要があります。
Excelのピボットテーブルのように「あるものを自動で並べる」ということにはなりません。


開始日は次のような方法で求められます
・2週間毎なら
　「パーティション」 - 「最初の日」 で日数を求める
　14で割る(小数切り捨て)と「集計ブロック番号」になる
　「最初の日」 + 14 * 「集計ブロック番号」 で、各ブロックの最初の日が求められる

　　　あるいは

　「パーティション」 - ( 「パーティション」 - 「最初の日」 を 14で割った余り)

・月毎なら
　「パーティション」の年 + 「パーティション」の月 + 1日 で、月初になる


まず、 閲覧コンテンツID, 集計開始日, ユーザーID(重複無し) になる副クエリにして、それを count(ユーザーID) するのがよいでしょう。
※HIVEの場合、 count( distinct) より、 重複無しのクエリ→distinct無しのcount の方が効率がいいらしいです


○ パーティション列は「文字列」でしょうか「日付」でしょうか?
文字列だったら

パーティション　between '2017/02/12' and '2017/02/25'

だと、正しく動かない気がしますが。

No.1 回答者： くんこば 回答日時： 2019/12/11 08:20

調べるのはご自分でしてくださいね。

・集計クエリを使いましょう
　　Group by〜
・日付を別の列で定義しましょう
　　例：year(日付)
・上二つが理解できたら、集計区分を別で定義した日付列にすれば上手くいくと思います。

月単位で集計したい、などで検索してみてください。