Sequelizeで "SELECT count(DISTINCT(`user_id`))"を実現する


Node.js + Sequelize で開発中・・・
重複したデータを除外したデータ数が欲しくなった。
「期間内にサインインしたユニークユーザ数が知りたい」とかそんな感じのこと。

ドキュメント探すのに時間がかかったので、未来の自分と誰かのために書き残します。


## テーブル

こんな感じのテーブルを使う想定です。
```sql
mysql> select * from access_logs;
+----+-----------+---------+---------+---------------------+
| id | tenant_id | user_id | type    | created_at          |
+----+-----------+---------+---------+---------------------+
|  1 |         1 |       2 | signin  | 2022-04-13 15:55:50 |
|  2 |         1 |       1 | signout | 2022-04-13 15:55:59 |
|  3 |         1 |       1 | signin  | 2022-04-01 00:00:00 |
|  4 |         1 |       2 | signout | 2022-04-14 09:52:43 |
|  5 |         2 |       2 | signin  | 2022-04-14 15:31:28 |
+----+-----------+---------+---------+---------------------+
```
(型やモデルは・・・推論してください☆)


## 方法1 distinct:true

一定の期間にサインインしたユーザ数(ユニーク)を取得したいなら、こんな感じで書けばOK。

```js
await AccessLog.count({
  where: {
    tenant_id,
    type: 'signin',
    created_at: { [Op.between]: ['2022-04-01', '2022-04-30'] },
  },
  distinct: true,  // <-重要
  col: 'user_id',  // <-重要
})
```

`distinct: true` を入れることでDISTINCT句を再現できます
`col: 'user_id'` は、primary key 以外のカラムでユニークにしたい場合に使います

↓SQL的にはこんな感じになってるはずです
```sql
SELECT
    count(DISTINCT(`user_id`)) AS `count`
FROM
    `access_logs` AS `AccessLog`
WHERE
    `AccessLog`.`tenant_id` = 1
AND `AccessLog`.`type` = 'signin'
AND `AccessLog`.`created_at` BETWEEN '2022-04-01' AND '2022-04-30'
;
```

#### `col: 'user_id'` を抜いたらどうなるか・・

primary keyでDISTINCTされます (`count(DISTINCT(`id`))`) 。
↓SQL的にはこんな感じです。
```sql
SELECT
    count(DISTINCT(`id`)) AS `count`
FROM
    `access_logs` AS `AccessLog`
WHERE
    略
;
```

## 方法2 literalを使う

↓例えばユニークなユーザ数を日毎に集計したい場合にはこう書きます。

```sql
await AccessLog.findAll({
  attributes: [
    [sequelize.fn('DATE_FORMAT', sequelize.col('created_at'), '%Y/%m/%d'), 'label'],
    [sequelize.literal(`COUNT(DISTINCT user_id)`), 'value1'],
    [sequelize.fn('COUNT', sequelize.col('id')), 'value2'],
  ],
  where,
  group: 'label',
  raw: true,
})

```

↓SQL的にはこんな感じです。
```sql
SELECT
    DATE_FORMAT(`created_at`, '%Y/%m/%d') AS `label`,
    COUNT(DISTINCT user_id) AS `value1`,
    COUNT(`id`) AS `value2`
FROM
    `access_logs` AS `AccessLog`
WHERE
    `AccessLog`.`tenant_id` = 1
AND `AccessLog`.`created_at` BETWEEN '2022-04-18 23:59:59' AND '2023-01-15 23:59:59'
GROUP BY
    `label`
;
```

↓こんな感じのデータが取れます
```sql
+------------+--------+--------+
| label      | value1 | value2 |
+------------+--------+--------+
| 2022/04/30 |      1 |      1 |
| 2022/05/02 |      2 |     11 |
| 2022/05/03 |      1 |     12 |
| 2022/05/06 |      2 |      9 |
| 2022/05/09 |      2 |     16 |
| 2022/05/10 |      1 |      1 |
| 2022/05/11 |      1 |      1 |
| 2022/05/16 |      1 |      1 |
| 2022/05/17 |      1 |      8 |
| 2022/05/19 |      2 |      3 |
| 2022/05/20 |      1 |      3 |
| 2022/05/24 |      1 |      1 |
| 2022/05/25 |      1 |      1 |
| 2022/05/27 |      2 |      9 |
| 2022/05/30 |      2 |      8 |
| 2022/05/31 |      1 |      1 |
| 2022/06/01 |      1 |      1 |
| 2022/06/02 |      1 |      1 |
| 2022/06/10 |      1 |     13 |
| 2022/06/13 |      1 |      1 |
| 2022/06/14 |      1 |      1 |
| 2022/06/15 |      2 |      7 |
| 2022/06/16 |      1 |      2 |
+------------+--------+--------+
23 rows in set (0.00 sec)
```




## まとめ
SequelizeでDISTINCTする方法を探してるとき、リテラルで書くなどの複雑な記事ばかり当ててしまい、この簡単な結論に行き着くのに時間がかかりました。
このメモが未来の自分やどなたかの助けになれば幸いです。



参考:


- [公式Refarance - static-method-count ](https://doc.esdoc.org/github.com/sequelize/sequelize/class/lib/model.js~Model.html#static-method-count)
- [stackoverflow - How to get a distinct count with sequelize?](https://stackoverflow.com/questions/22643263/how-to-get-a-distinct-count-with-sequelize)