sql子查询行按列分组

qxgroojn 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(357)

我想在每个人的回复中都有专栏 task_type 计数按分组 date_trunc('day') 以及 user_id . 因此，一旦整个查询运行，它将返回 task_type_1 列和字段值将是给定用户在给定日期具有该类型的任务数。
到目前为止，我已经运行了这个命令，但不确定如何将任务类型分组添加到此查询中：

SELECT users.id AS user_id,
  date_trunc('day', workforce_assigned_tasks.created_at) AS day,
  SUM(workforce_assigned_tasks.duration) AS duration,
  SUM(workforce_assigned_tasks.earnings_cents) AS earnings_cents,
  SUM(workforce_assigned_tasks.subtask_count) AS subtask_count,
  WHAT GOES HERE?
FROM users
JOIN workforce_assigned_tasks ON workforce_assigned_tasks.user_id = users.id
JOIN workforce_tasks ON workforce_assigned_tasks.workforce_task_id = workforce_tasks.id
GROUP BY date_trunc('day', workforce_assigned_tasks.created_at), users.id;

sql postgresql aggregate-functions grouping

来源：https://stackoverflow.com/questions/62687447/sql-subquery-rows-as-group-by-columns

1条答案

按热度按时间

u7up0aaq1#

您可以使用条件聚合，它在postgres中使用 FILTER 条款：

SELECT u.id AS user_id, date_trunc('day', wat.created_at) AS day,
       SUM(wat.duration) AS duration,
       SUM(wat.earnings_cents) AS earnings_cents,
       SUM(wat.subtask_count) AS subtask_count,
       COUNT(*) FILTER (WHERE wt.task_type_1 = 1)
FROM users u JOIN
     workforce_assigned_tasks wat
     ON wat.user_id = u.id JOIN
     workforce_tasks wt
     ON wat.workforce_task_id = wt.id
GROUP BY date_trunc('day', wat.created_at), u.id;

我猜是的 task_type 在 workforce_tasks .
请注意，表别名的使用使查询更易于编写和读取。

赞(0）回复(0）举报 2021-07-29

我来回答

sql子查询行按列分组

1条答案

相关问题

热门标签

最新问答