sql - 如何强制 SQL 查询计数 max/min/avg 正常工作？

Question

我在编写正确的 sql 查询时遇到问题，该查询应按国家和站点选择两个日期之间的持续时间的最小/最大/平均天数。

我的查询:

SELECT v.country as country, v.site as site,
  COUNT(*) as N --,
  --MAX(list of durations in days between discovered date to repored date on each violation by country and site) as "Maximum",
  --MIN(list of durations in days between discovered date to repored date on each violation by country and site) as "Minimum",
  --AVG(list of durations in days between discovered date to repored date on each violation by country and site) as "Mean"
FROM violations v
WHERE v.trial_id = 3
GROUP BY ROLLUP (v.country, v.site)

这是我的 SQLFIDDLE具有改进注释行所需的数据结构和查询。

你能帮帮我吗？

Answer 1

在 Oracle 中，日期存储为偏移量，因此您可以简单地从另一个日期中减去一个日期。参见 Date arithmetic获取更多信息。

SQLFiddle

SELECT v.country as country, v.site as site,
  COUNT(*) as N,
  MAX(reporded_date - discovered_date) as "Maximum",
  MIN(reporded_date - discovered_date) as "Minimum",
  AVG(reporded_date - discovered_date) as "Mean"
FROM violations v
WHERE v.trial_id = 3
GROUP BY ROLLUP (v.country, v.site)

请注意，您提供的测试数据始终相隔 1 天，因此我也在我的 fiddle 中更新了它(针对德国)。