SQLが作れません...【window関数 COUNT(DISTICT)を代替する方法はないか?】

Question

いつもお世話になっております。
SQLの作成につまづいており、どなたかご教授いただきたいです。
詳細は以下になります。

<環境>
・クエリ実行環境:TresureData(ビッグデータ集計ツール)
・SQL:presto

<求めたいクエリ>
・ゲームのKPIを求めるクエリです。
・日毎の課金UUの累計を、プラットフォーム毎(iOS,Android)に求めます。
・具体例を示すと、次のような感じです。
date platform uu
2016-01-01 ios 100
2016-01-01 android 70
2016-01-02 ios 120 <- 前日からだんだん累計されていく
2016-01-02 android 130

<困っている点>
・window関数を使って、課金UUの累計を求めようとしています。
ただ、prestoだと、window関数で DISTINCTを使えないようです...。
window関数を代替して、どうにか求める方法はないでしょうか?

<実際のクエリ>

-- 日別プラットフォーム別に、課金したユーザの一覧を取る
WITH sub1 AS
(
SELECT
  TD_TIME_FORMAT(time,'yyyy-MM-dd','JST') AS date
  ,{platform}
  ,user_id
FROM
  kpi_table
WHERE
  --月初め〜月末まで取る
  TD_TIME_RANGE(
    time
    ,TD_TIME_FORMAT({month_start},'yyyy-MM-dd 00:00:00','JST')
    ,TD_TIME_FORMAT({month_last},'yyyy-MM-dd 23:59:59','JST')
    ,'JST'
  )
  --課金したユーザのみ
  AND (purchase_amount > 0)
GROUP BY
  TD_TIME_FORMAT(time,'yyyy-MM-dd','JST')
  ,{platform}
  ,user_id
ORDER BY
  TD_TIME_FORMAT(time,'yyyy-MM-dd','JST') ASC
  ,{platform} ASC
  ,user_id
)
-- 集計
SELECT
  date
  ,{platform}
  -- Window関数で DISTINCTは未実装のため、ここでエラーになる
  ,COUNT(DISTINCT user_id) OVER(PARTITION BY {platform} ORDER BY date ASC) AS charging_uu
FROM
  sub1
ORDER BY
  date
  ,{platform}
--...(実際のクエリは、この後も処理が続きます...)

<補足>
・クエリ中の{}で囲っている箇所は、ツールを使って動的に値が入ります。
・代替案として、クエリを日別に生成して(ex 7/1までのSQL、7/2までのSQL、、、)
順にクエリを実行する方法も考えました。
ただ、パフォーマンスに影響する都合上、
クエリの実行は1回に抑えたく、この代替案は取れません。
・クエリはjupyter上から実行しています。
途中まで計算した段階で
pandas-tdライブラリのDataFrameクラスを使い、
jupyter上で集計を行うのもありかと思います。
ただ、jupyterを実行するマシンは、そこまでスペックが高くなく、
jupyter上で集計しようとするとパフォーマンスがでないと思います。
できれば、クエリ上で集計を全て行いたいです。

以上になります。
SQLに詳しい方、ビックデータに詳しい方etc...
ご教授いただけますと幸いですb

20160728追記

• 一応、window関数とWHERE句を使って書くことはできました(以下になります)。
• まだ冗長なクエリなので、何かスマートな書き方をご存知な方がいれば、教えていただけますと幸いです...b

-- 日別プラットフォーム別に、課金したユーザの一覧を取る
WITH sub1 AS
(
SELECT
  TD_TIME_FORMAT(time,'yyyy-MM-dd','JST') AS date
  ,{platform}
  ,user_id
FROM
  kpi_table
WHERE
  --月初め〜月末まで取る
  TD_TIME_RANGE(
    time
    ,TD_TIME_FORMAT({month_start},'yyyy-MM-dd 00:00:00','JST')
    ,TD_TIME_FORMAT({month_last},'yyyy-MM-dd 23:59:59','JST')
    ,'JST'
  )
  --課金したユーザのみ
  AND (purchase_amount > 0)
GROUP BY
  TD_TIME_FORMAT(time,'yyyy-MM-dd','JST')
  ,{platform}
  ,user_id
ORDER BY
  TD_TIME_FORMAT(time,'yyyy-MM-dd','JST') ASC
  ,{platform} ASC
  ,user_id
)
-- ユーザ毎のログイン日を集計
, sub2 AS
(
SELECT
  date
  ,{platform}
  ,user_id
  ,COUNT(user_id) OVER (PARTITION BY {platform}, user_id ORDER BY date) AS login_count
FROM
  sub1
ORDER BY
  date ASC
  ,{platform} ASC
  ,user_id
)
-- 全ユーザのログイン日を集計
, sub3 AS
(
SELECT
  date
  ,{platform}
  ,COUNT(user_id) OVER (PARTITION BY platform ORDER BY date) user_num
FROM
  sub2
--最初にログインした日以外のデータは排除
WHERE
  login_count = 1
ORDER BY
  date ASC
  ,{platform} ASC
)
-- データを日別 * プラットフォーム別に絞る
SELECT
  date
  ,{platform}
  MAX(user_num) AS user_num
FROM
  sub3
GROUP BY
  date
  ,{platform}
ORDER BY
  date ASC
  ,{platform} ASC

Comments

[Panzer_vor]

こんばんは。
prestoノータッチなんですが一点気になったので質問をば。

WITH句内のクエリでuser_idがGROUP BY対象となってないのに、
SELECT句で選択されています。
これはクエリがエラーとならないとしても、
取得するuser_idが不定となり意図したものと異なる値が取得されていたりなどしませんでしょうか？

[th1209]

KotoriMaturiさん
ご指摘ありがとうございます。
おっしゃるとおりで、WITH句内のGROUP BY句にはuser_idが入りますね...。
(転記する際に間違えていたみたいです。修正しておきますね！)

Answer 1

以下のようにCOUNT文の中のDISTINCTを除いてはダメですか？

COUNT(user_id) OVER(PARTITION BY {platform} ORDER BY date ASC) AS charging_uu

sub1のWITH句の中で、GROUP BY 句にuser_idを指定しているので、user_idは重複していないように思います。
なので、DISTINCTを取り払っても良いのでは？と思いました。

Comments

[th1209]

yuji38kwmtさん

ご回答ありがとうございます！
(ご回答に気づくのが遅れてしまい、申し訳ありません...。)

おっしゃる通り、先行するWITH句で、user_id毎にGROUP BYをかけているため、
そもそもDISCINCT要りませんよね(笑)

先ほど試しに実行してみたのですが、これで問題なく動作しました！
おかげで、この箇所のクエリ、すっきりしたものに直せそうです。