78325


以下のようなテーブルがあった際に、月単位（下記の例なら2021年1月）において、時間帯毎のユニークなcustomer_idのカウント、注文数を出すにはどのようなクエリで実現可能でしょうか。

table:

[code]

    order_timestamp, customer_id, order_id
    2021-01-01 23:31:31,aaa,111
    2021-01-02 01:22:11,bbb,123
    2021-01-31 07:20:55,aaa,444
    
[/code]

customer_idは同じcustomer毎に振られたユニークなID、order_idは注文毎のユニークなIDとなります。

例えばAというユーザーが、2021年1月に朝の8時に3回購入していた場合、  
8時のorder_countsとしては「3」となりますが、unique_customer_countsとしては「1」となります。

期待する結果としては、時間帯毎にどれくらいの頻度で購入されているかがわかるデータとなり、どの時間帯にリピーターが多いかを探りたいと考えています。

以下のようなクエリを実行すれば実現できるのではと思い試しましたが、なぜかorder_countsとunique_customer_countsが同じ数字になってしまいます。

もしこの結果が正しいのであれば、各customerがその時間帯には1ヶ月に1回しか購入していないことになり、不自然な結果となってしまいます。

[code]

    SELECT
    date_trunc('hour', order_timestamp) as　hour
    COUNT(order_id) as order_counts
    COUNT(distinct customer_id) as unique_customer_counts
    
    FROM table
    
    WHERE 1=1 
    AND MONTH(order_timestamp) = 1
    AND YEAR(order_timestamp) = 2021
    
    GROUP BY 1
    
[/code]

念の為Selectのdate_truncの部分を抜いてみると、order_countsとunique_customer_countsに明確な差が出ますので、データ自体が間違っているということはないようです。

[code]

    SELECT
    COUNT(order_id) as order_counts
    COUNT(distinct customer_id) as unique_customer_counts
    
    FROM table
    
    WHERE 1=1 
    AND MONTH(order_timestamp) = 1
    AND YEAR(order_timestamp) = 2021
    
[/code]


CC BY-SA 4.0
2021-07-20T13:11:25.907
78324
2021-07-20T16:27:57.063
2021-07-20T16:27:57.063
3060
23420
question
sql
時間帯毎のユニークなユーザーと合計注文数を確認したい


時間帯でグループ化すれば良いのではないでしょうか。  
以下、`MySQL 8.0` での実行結果です。

**table schema**

[code]

    CREATE TABLE tbl (
      order_timestamp datetime,
      customer_id varchar(3),
      order_id int
    );
    
[/code]

**test records**

[code]

    INSERT INTO tbl VALUES
    ('2021-01-01 08:50:00', 'A', 111),
    ('2021-01-02 01:22:11', 'B', 123),
    ('2021-01-10 08:30:00', 'A', 222),
    ('2021-01-25 08:10:00', 'A', 333),
    ('2021-01-31 23:20:55', 'C', 444)
    
[/code]

**sql query statement**

[code]

    SELECT
      HOUR(order_timestamp) AS hour,
      COUNT(order_id) AS order_counts,
      COUNT(distinct customer_id) AS unique_customer_counts
    FROM tbl
    WHERE 1=1 
      AND MONTH(order_timestamp) = 1
      AND YEAR(order_timestamp) = 2021
    GROUP BY hour ORDER BY hour
    
[/code]

**query result**

hour | order_counts | unique_customer_counts  
---|---|---  
1 | 1 | 1  
8 | 3 | 1  
23 | 1 | 1


CC BY-SA 4.0
2021-07-20T14:38:24.380
78325
2021-07-20T14:41:05.920
2021-07-20T14:41:05.920
47127
47127
78324
answer