Bigqueryで累計課金を集計してみた

#まずは、当月の日別累計課金を集計

例えば、こんなサンプルデータがあったとします。

| user_id  | purchase_date     | purchase_log |
|----------|----------|----------------|
| xerfe002 | 2019/1/1 | 1152          |
| 689ff003 | 2019/1/1 | 768           |
| ch83f004 | 2019/1/1 | 1152          |
| zqrme005 | 2019/1/1 | 384           |
| dbnsu006 | 2019/1/1 | 1920          |
| 6qagk007 | 2019/1/1 | 3072          |
| c1u54008 | 2019/1/1 | 384           |
| vot6t009 | 2019/1/1 | 3072          |
| 6uruk00a | 2019/1/1 | 384           |
| a36g800b | 2019/1/1 | 3072          |
| tlq9u00c | 2019/1/1 | 3840          |
| clvw700d | 2019/1/1 | 1920          |
| dj3x200e | 2019/1/2 | 384           |
| 8gvg600f | 2019/1/2 | 384          |
| abywv00g | 2019/1/2 | 384           |
| …        |          |                |

日別の課金だけではなく、月別の売り上げ累計も同時に見たいときがあります。
そんなときには、ウィンドウ関数を使用します。

```sql
SELECT 
purchase_date
,SUM(purchase_log) AS total
,SUM(SUM(purchase_log))
 OVER(PARTITION BY substr(purchase_date,1,7) ORDER BY date ROWS UNBOUNDED PRECEDING)
 AS agg_amount
FROM purchase_hist
GROUP BY
purchase_date
ORDER BY
purchase_date

```

| purchase_date | daily_amount | agg_amount |
|---------------|--------------|------------|
| 2019/1/1      | 363008       | 363008     |
| 2019/1/2      | 180197       | 543205     |
| 2019/1/3      | 104379       | 647583     |
| 2019/1/4      | 139113       | 786697     |
| 2019/1/5      | 101877       | 888574     |
| 2019/1/6      | 64336        | 952910     |
| 2019/1/7      | 31824        | 984733     |
| 2019/1/8      | 7129         | 991863     |
| 2019/1/9      | 13029        | 1004892    |
| 2019/1/10     | 35131        | 1040024    |
| 2019/1/11     | 135328       | 1175352    |
| 2019/1/12     | 73800        | 1249151    |
| 2019/1/13     | 66076        | 1315227    |
| 2019/1/14     | 36149        | 1351377    |
| 2019/1/15     | 30199        | 1381576    |

上記のSQLで、日別の売り上げと月別の累計売上を同時に集計しているため、PARTITION BY substr(date,1,7)と書いています。


#当月の日別累計課金をユーザ別に集計
次にユーザー別の売り上げが見たい時の修正をご紹介します。
ユーザ別の累計売上が見たい、ユーザ毎に累計売り上げ金額でセグメンテーションをしたい場合は下記にように書く必要があります。

```sql
SELECT 
user_id
,purchase_date
,SUM(purchase_log) AS total
,SUM(SUM(purchase_log)) 
 OVER(PARTITION BY osuser_id, substr(purchase_date,1,7) ORDER BY purchase_date ROWS UNBOUNDED PRECEDING)
 AS agg_amount
FROM purchase_hist
GROUP BY
user_id
,purchase_date
ORDER BY
user_id
,purchase_date
```

| user_id | purchase_date | daily_amount | agg_amount |
|-----------|--------------|--------------|------------|
| 7ju9c002  | 2019/1/1     | 1960         | 1960       |
| 7ju9c002  | 2019/1/14    | 320          | 2280       |
| 7ju9c002  | 2019/1/15    | 600          | 2880       |
| 7ju9c002  | 2019/1/16    | 96           | 2976       |
| 7ju9c002  | 2019/2/6     | 96           | 96         |
| 7ju9c002  | 2019/2/7     | 320          | 416        |
| 7ju9c002  | 2019/2/8     | 320          | 736        |
| 7ju9c002  | 2019/2/10    | 320          | 1056       |
| 7ju9c002  | 2019/2/11    | 2560         | 3616       |
| 7ju9c002  | 2019/2/12    | 168          | 3784       |
| 7ju9c002  | 2019/2/13    | 416          | 4200       |
| 7ju9c002  | 2019/2/23    | 1160         | 5360       |
| 7ju9c002  | 2019/1/1     | 120          | 120        |
| 7ju9c003  | 2019/1/1     | 1160         | 1160       |
| 7ju9c003  | 2019/2/1     | 1160         | 1160       |
| 7ju9c003  | 2019/2/7     | 600          | 1760       |
| 7ju9c003  | 2019/2/2     | 600          | 600        |
| 7ju9c004  | 2019/1/1     | 13520        | 13520      |
| 7ju9c004  | 2019/1/2     | 600          | 14120      |
| 7ju9c004  | 2019/1/3     | 1984         | 16104      |
| …         |              |              |            |


上記のSQLで、ユーザ別の日別の売り上げと月別の累計売上を同時に集計しているため、PARTITION BY user_id, substr(date,1,7)と書く必要があります。