摘要查询返回重复值MySQL

Question

我必须为我的系统上请求的门票做一份报告。

这些票据是从工作站调用的，并且该工作站与许多区域相关。

  SELECT
   DATE(q.created_at)   AS DATE,
   HOUR(q.created_at)   AS HOUR,
   ar.name              AS area,
   w.name               AS workstation,
   COUNT(DISTINCT q.id) AS count
  FROM qms_queue q
  JOIN qms_workstations w ON q.workstation_id = w.id
  JOIN qms_area_workstation aw ON aw.workstation_id = w.id
  JOIN qms_areas ar ON aw.area_id = ar.id
  WHERE q.created_at
      BETWEEN '2018-02-26 00:00:00'
      AND '2018-02-28 23:59:59'
      AND customer_id = 1
  GROUP BY DATE(q.created_at), w.id, HOUR(q.created_at)
  ORDER BY DATE(q.created_at), HOUR(q.created_at)

执行该查询后，我得到了票证的总计数。

  SELECT
   DATE(q.created_at)   AS DATE,
   HOUR(q.created_at)   AS HOUR,
   ar.name              AS area,
   w.name               AS workstation,
   COUNT(DISTINCT q.id) AS count
  FROM qms_queue q
  JOIN qms_workstations w ON q.workstation_id = w.id
  JOIN qms_area_workstation aw ON aw.workstation_id = w.id
  JOIN qms_areas ar ON aw.area_id = ar.id
  WHERE q.created_at
      BETWEEN '2018-02-26 00:00:00'
      AND '2018-02-28 23:59:59'
      AND customer_id = 1
  GROUP BY DATE(q.created_at), w.id, HOUR(q.created_at), ar.id
  ORDER BY DATE(q.created_at), HOUR(q.created_at)

但是，执行该查询将获得每个区域的绝对总数。

我想不出一个方法来计算每个区域的部分计数。

有人能帮我一下吗？

Answer 1

在看不到实际数据的情况下，考虑简单地运行不同的聚合，一个在区域级别，另一个在工作站级别，而不是两者都运行。如果需要，按日期和小时将两者作为派生表子查询进行连接

区域级

SELECT
    DATE(q.created_at)   AS `DATE`,
    HOUR(q.created_at)   AS `HOUR`,
    ar.name              AS `area`,
    COUNT(DISTINCT q.id) AS `area_count`
...
GROUP BY DATE(q.created_at), HOUR(q.created_at), ar.name
ORDER BY DATE(q.created_at), HOUR(q.created_at)

工作站级

SELECT
   DATE(q.created_at)   AS `DATE`,
   HOUR(q.created_at)   AS `HOUR`,
   w.name               AS `workstation`,
   COUNT(DISTINCT q.id) AS `ws_count`
...
GROUP BY DATE(q.created_at), HOUR(q.created_at), w.name
ORDER BY DATE(q.created_at), HOUR(q.created_at)

Answer 2

您好，我没有您的数据设置，但尝试分析函数为此

COUNT(q.id) OVER (PARTITION BY  ar.name)

如果它正在为每个区域提供计数，请选中此选项。