postgresqlで日毎に通算のユニークユーザー数を算出したい

Question

前提・実現したいこと

取引テーブル（tx）を元に毎日その日のユニークユーザー（UU）数とその日までの通算のUU数をpostgresqlで集計したいです。

元となるデータ

id	user_id	date
1	1	1970-01-01
2	1	1970-01-01
3	2	1970-01-01
4	3	1970-01-01
5	4	1970-01-02
6	5	1970-01-02
7	1	1970-01-02
8	2	1970-01-03
9	6	1970-01-03

期待する結果

年月日	日別UU	通算UU
1970-01-01	3	3
1970-01-02	3	5
1970-01-03	2	6

通算UUでは、それぞれの日までに取引実績がある人のユニーク数を算出したいです。
1970-01-01までに3人（1,2,3）、1970-01-02までに5人（1,2,3,4,5）、1970-01-03までに6人（1,2,3,4,5,6）というのを求めたいです。

発生している問題・エラーメッセージ

Error running query: DISTINCT is not implemented for window functions

該当のソースコード

SELECT
date
, count(distinct user_id) over(ORDER BY date)
FROM tx
GROUP BY date
ORDER BY date ASC

試したこと

上記コードはウィンドウ関数でdistinctが使えないため不可ということで色々調べたりしましたが、日毎に通算を算出する方法がわかりませんでした。

Comments

[surface_0]

期待する通算UUは3,5,6となっていますが、3,6,8ではないのですか？

[teratail_k]

すみません。補足が遅くなりました。teratailで質問するのは初めてでこんなに早く回答をいただけるものとは思っていませんでした。すごいですね。
通算UUの期待する結果は3,5,6です。
それぞれの日までに取引実績がある人のユニーク数を算出したかったので。その説明をちゃんと書くべきでしたね。
1970-01-01までに3人（1,2,3）、1970-01-02までに5人（1,2,3,4,5）、1970-01-03までに6人（1,2,3,4,5,6）というのを求めたいです。
時間かかってしまいますがいただいている回答を1つずつ確認します。

Answer 1

他の方の回答と内容的には同じでネストが少ないだけですが。

select date 年月日
     , count(distinct user_id) 日別UU	
     , (select count(distinct user_id) from tx where date<=t.date) 通算UU
from tx t
group by date

Comments

[teratail_k]

簡潔なクエリながら理解が追いついていません。
where date<=t.date
この箇所の解説をお願いしたいです。

[sazi]

「相関副問合せ」と言われるものです。
ネットに詳しい解説が沢山あります。

[sazi]

>ウィンドウ関数でdistinctが使えないため
windouw関数はコストを下げる為にgroup byをしない事を目的としたものですから、distinctしたいという事は、Window関数の使用は目的に合っていません。

Answer 2

結合や相関サブクエリを使わずにwindow関数のみで実現する方法を紹介します。

通算UU でuser_idの重複を除いてカウントするために、user_idが初めて登場した行に初登場のフラグを立てます

select 
  id
, user_id
, date
, case when first_value(id) over(partition by user_id order by date, id) = id
       then 1 else 0 end as 初登場
from tx
order by id

結果

id	user_id	date	初登場
1	1	1970-01-01	1
2	1	1970-01-01	0
3	2	1970-01-01	1
4	3	1970-01-01	1
5	4	1970-01-02	1
6	5	1970-01-02	1
7	1	1970-01-02	0
8	2	1970-01-03	0
9	6	1970-01-03	1

後はwindow関数のsum()を使って初登場の列の値を累積すれば、通算UUの値を求めることが出来ます。

上記の手法を使って、やりたいことを実現するSQLは以下となります。

with sub1 as 
(
select 
  user_id
, date
, case when first_value(id) over(partition by user_id order by date) = id
       then 1 else 0 end as 初登場
from tx
), sub2 as 
(
select
  date
, count(distinct user_id) as 日別UU
, sum(初登場) as 通算UU
from sub1
group by date
)

select
  date
, 日別UU
, sum(通算UU) over(order by date) as 通算UU
from sub2
order by date

Comments

[mayu-]

このロジック、いいと思います。
私の記述は、日別UU, 通算UU 単体だと問題ないのですけど
JOINに欠陥があるので。
( 現存の user_id のみで新規の user_id が無い日はデータが欠ける )
( それを防ぐ方法もあるにはありますけど、クエリが重くなるのがネック )

[mayu-]

window関数は、集計関数の結果にかぶせることが可能ですので
よりシンプルに記述できそうですね。少しチューニングしてみました。

with sub1(user_id, date, 初登場) as
(
select
user_id
, date
, case when first_value(id) over(partition by user_id order by date, id) = id
then 1 else 0 end
from tx
)
select
date
, count(distinct user_id) as 日別UU
, sum( sum(初登場) ) over ( order by date ) 通算UU
from sub1
group by date

Answer 3

概念的に一番分かりやすい形はこうじゃないのかな。
このSQLはパフォーマンス的にどうなんだ、って話はあるかもしれないけれど……

SELECT
     date  AS 年月日
   , cnt   AS 日別UU
   , (SELECT count(distinct user_id) FROM hogehoge b WHERE b.date <= a.date) AS 通算UU
FROM
 (SELECT
       date
     , count(distinct user_id) AS cnt
  FROM hogehoge
  GROUP BY
       date
 ) a

Comments

[mayu-]

私の記述が今のところ、分かりやすさでは断トツ最下位なので
ishina_yumさんのロジック、分かりやすくて良いと思います。

Answer 4

もしかすると

count(distinct user_id) over(ORDER BY date)

じゃなく

count(user_id) over(partition by "date")

だったりしないかな？ 日付ごとのuser_idをカウントしたいなら。

Answer 5

その日までの通算のUU数

「通算」が、どのような演算や集計方法で
ご提示の値になるのか解釈に悩みましたけど
推理すると以下のようなことでしょうか。

マスタではなく、トランザクションテーブルにおいて
固有のuser_idが初めて登場した年月( サンプルデータでは日ですが )を
user_idの「登録日」と見なして
( 過去月を含む )当日までの新規user_idの総数( 累計 )を計算したい。

カウントしたいのはあくまで新規なので
過去日に登場したuser_idは当日に再度現れてもカウントしない。
＃ 実績のあるuser_idの個体数を算出したいのではないか、と推測

また、初回登録された日にuser_idがユニークな値にならないのは
実際は、同日に複数のアクション( 商品購入など )が発生していたりするのでしょう。
ひょっとするとdateも「時刻」が切り捨てられていて
元のdateは別の値なのかもしれませんね。

##更に追記:
シンプルにできたかも。※ver9.4以降

SELECT ymd 年月日
     , count( distinct user_id ) 日別UU
     , sum( count( distinct user_id ) filter( where ymd = f ) ) 
           over( order by ymd ) 通算UU
FROM
(
    SELECT tx."date" ymd
         , user_id
         , min( tx."date" ) over( partition by user_id ) f
    FROM tx
) q
GROUP BY ymd ;

##追記：
新規登録のuser_idが存在しない日でも
日別UU と 通算UU が両方とも表示されるよう外部結合を適用。

CREATE TABLE tx
(
      id        serial primary key
    , user_id   int    not null
    , "date"    date   not null
);

INSERT INTO tx
    ( user_id, "date" )
VALUES
    ( 1, '1970-01-01' )
  , ( 1, '1970-01-01' )
  , ( 2, '1970-01-01' )
  , ( 3, '1970-01-01' )
  , ( 4, '1970-01-02' )
  , ( 5, '1970-01-02' )
  , ( 1, '1970-01-02' )
  , ( 2, '1970-01-03' )
  , ( 6, '1970-01-03' )
  , ( 2, '1970-01-04' )
  , ( 2, '1970-01-04' )
  , ( 3, '1970-01-05' )
  , ( 7, '1970-01-06' )
;

SELECT x.ymd 年月日
     , x.duu 日別UU
     , max( y.tuu ) over( order by x.ymd ) 通算UU
FROM
(
    SELECT tx."date" ymd
         , count( distinct user_id ) duu
    FROM tx
    GROUP BY tx."date"
) x
LEFT JOIN
(
    SELECT DISTINCT
           ymd
         , count(1) over( order by ymd ) tuu
    FROM
    (
        SELECT min( tx."date" ) ymd
        FROM tx
        GROUP BY user_id
    ) q
) y
USING ( ymd )
;

推理が当たっていた場合に限りますけど
通算UUをわかりやすく計算するのなら　←そんなことは無かった
以下のような３段のクエリになりそうです。

SELECT 年月
     , sum( 新規uid数 ) 
           over( order by 年月
                 rows between unbounded preceding and current row 
       ) 通算UU
FROM
(
    SELECT 年月
         , count(1) 新規uid数
    FROM
    (
        SELECT user_id
             , min( t."date" ) 年月
        FROM テーブル名 t
        GROUP BY user_id
    ) q2
    GROUP BY 年月
) q1
;

Answer 6

通算UUは3,6,8でよければサブクエリを使った形でいけます。

SELECT
  date AS 年月日,
  dau AS 日別UU,
  SUM(dau) OVER(ORDER BY date) AS 通算UU
FROM (
  SELECT date, COUNT(DISTINCT user_id) AS dau
  FROM tx
  GROUP BY date
) S
ORDER BY date ASC

Answer 7

postgresはよく知らないけど通算なら質問にもある通り3,6,8じゃないですか？
あとcountをcountする意味はないのでsumじゃないですか？
通るかはわかりませんがこんな感じじゃないですか

select X.date,X.通算UU
  , sum(X.通算UU) over(ORDER BY date)
from (
  SELECT date
    , count(distinct user_id) as 通算UU
  FROM tx
  GROUP BY date
) as X
ORDER BY X.date ASC