MySQL 查询缺少行

Question

所以我需要一点帮助。我似乎找不到正确的 JOIN 或任何我需要的东西来获得我正在寻找的所有结果。

我当前的查询如下:

SELECT
    COUNT(hd.value) as total_rows,
    ROUND(AVG(hd.value), 2) as OEE_AVG,
    dl.line_id,
    hd.shift,
    ol.description
FROM
    dashboard_to_dashboard_lines ddl
JOIN dashboard_lines dl ON dl.id = ddl.dashboard_line_id
LEFT OUTER JOIN operation_lines ol ON (dl.line_id = ol.line_id)
JOIN historical_data hd ON dl.gauge_tag_id = hd.tag_id
WHERE
    hd.timestamp BETWEEN '2016-01-04 00:00:00' AND '2016-01-04 23:59:59'
    AND ddl.dashboard_id = 1
    AND hd.value < 200
GROUP BY
    dl.line_id,
    hd.shift
ORDER BY
    hd.shift,
    dl.line_id ASC

期望的结果如下:

________________________________________________________
| total rows | oee_avg | line_id | shift | description |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 858        | 80.24   |  1      |  1    | Line Desc   |
| 432        | 60.21   |  2      |  1    | Line Desc   |
| 234        | 70.75   |  3      |  1    | Line Desc   |
| 865        | 10.45   |  1      |  2    | Line Desc   |
| 864        | 20.26   |  2      |  2    | Line Desc   |
| 823        | 30.44   |  3      |  2    | Line Desc   |
| 423        | 56.14   |  1      |  3    | Line Desc   |
| 234        | 12.74   |  2      |  3    | Line Desc   |
| 534        | 43.64   |  3      |  3    | Line Desc   |

基本上，我获取与按类次分隔的仪表板关联的每行值的平均值。

我主要获取所需的数据，除了 1 行、1 个类次之外的数据。 (其他时间范围可以为所有类次的所有线路提供所需的结果)。所以我假设那里没有数据用于计算，这导致该行被遗漏。

我尝试进行外连接(在查询中混合和匹配)，但没有成功。

我知道没有 SQL fiddle 很难，但我希望有人能有一些见解来帮助我。

提前致谢。

SQL Fiddle

编辑:添加了 SQL Fiddle 链接

Answer 1

没有示例数据，我无法测试，但如果它不能完全工作，这应该为您指明正确的方向。重要的是，您需要所有可能的线路/类次组合的来源。在本例中，我对您的表使用了子查询，不受日期等限制，但您可能有更好的方法来获取这些信息。此外，在类似情况下，对于某些您仍希望在报表上查看内容的值，数据可能根本不存在。例如，如果我按天报告销售额，一家商店周日关门，但我仍然希望看到当天的零。在这种情况下，除了从现有表中选择之外，您还需要通过其他方式创建值。

SELECT
    COUNT(hd2.value) AS total_rows,
    ROUND(AVG(hd.value), 2) AS OEE_AVG,
    SQ.line_id,
    SQ.shift,
    OL.description
FROM
    (
        SELECT DISTINCT dl.line_id, hd.shift
        FROM dashboard_lines dl
        INNER JOIN historical_data hd ON hd.tag_id = dl.gauge_tag_id
    ) SQ
INNER JOIN dashboard_to_dashboard_lines ddl ON ddl.dashboard_id = 1
LEFT OUTER JOIN dashboard_lines dl2 ON
    dl2.id = ddl.dashboard_line_id AND
    dl2.line_id = SQ.line_id
LEFT OUTER JOIN historical_data hd2 ON
    hd2.tag_id = dl.gauge_tag_id AND
    hd2.shift = SQ.shift AND
    hd2.timestamp BETWEEN '2016-01-04 00:00:00' AND '2016-01-04 23:59:59' AND
    hd2.value < 200
LEFT OUTER JOIN operation_lines ol ON ol.line_id = dl2.line_id
GROUP BY
    SQ.line_id,
    SQ.shift
ORDER BY
    SQ.shift,
    SQ.line_id ASC

另外，请注意，如果 MySQL 要保持超过秒的精度，则对时间戳列的检查可能会导致错误结果。更好用hd2.timestamp >= '2016-01-04 00:00:00' AND hd2.timestamp < '2016-01-05 00:00:00'