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

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

テーブル

こんな感じのテーブルを使う想定です。

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。

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的にはこんな感じになってるはずです

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的にはこんな感じです。

SELECT
    count(DISTINCT(`id`)) AS `count`
FROM
    `access_logs` AS `AccessLog`
WHERE
    略
;

方法2 literalを使う

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

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的にはこんな感じです。

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`
;

↓こんな感じのデータが取れます

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

参考: