質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.12%

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,354

th1209

score 34

いつもお世話になっております。
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
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Panzer_vor

    2016/07/27 22:26

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

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

    キャンセル

  • th1209

    2016/07/28 11:33

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

    キャンセル

回答 1

checkベストアンサー

+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を取り払っても良いのでは?と思いました。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/08/28 18:06

    yuji38kwmtさん

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

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.12%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる