我们可以在Oracle sql中编写group-by来获取订单的第一条记录吗?或者你能为给定的查询提出解决方案吗

Question

我有一个表，每天显示设备的好、坏和其他状态。我想为每个设备显示一行，其中包含今天的状态和以前的最佳状态(如果时间跨度内任何时间都很好，则为"好"，否则为前一天的状态(。我正在使用联接，查询如下所示。

SELECT t1.devid,
t1.status AS Today_status,
t2.status AS yest_status,
t2.runtime AS yest_runtime
FROM devtable t1
INNER JOIN devtable t2
ON     t1.devid = t2.devid
AND t1.RUNTIME = '17-jul-2018'
AND t2.runtime > '30-jun-2018'
ORDER BY t1.devID, (CASE WHEN t2.status LIKE 'G%' THEN 0 END), t2.runtime;

现在，我无法将其分组为每个设备的单个记录(每个设备有许多记录(。你能就此提出解决方案吗？

Answer 1

这将更容易用样本数据和结果来解释，但听起来你想要这样的东西：

select devid, runtime, status, prev_status,
coalesce(good_status, prev_status) as best_status
from (
select devid, runtime, status,
lag(status) over (partition by devid order by runtime) as prev_status,
max(case when status = 'Good' then status end) over (partition by devid) as good_status
from (
select devid, runtime, status
from devtable
where runtime > date '2018-06-30'
)
)
where runtime = date '2018-07-17';

最里面的查询限制日期范围；如果你需要一个上限(即不是今天的例子(，那么把它作为另一个过滤器。

下一层使用lag()和max()分析函数为每个ID查找以前的状态和任何"良好"状态(通过事例表达式(。

然后，外部查询进行筛选，只显示目标结束日期，如果存在，则使用coalesce()显示"良好"，如果不存在，则显示以前的状态。

在CTE:中演示一些虚构的样本数据

with devtable (devid, runtime, status) as (
select 1, date '2018-06-30', 'Good' from dual -- should be ignored
union all select 1, date '2018-07-01', 'a' from dual
union all select 1, date '2018-07-16', 'b' from dual
union all select 1, date '2018-07-17', 'c' from dual
union all select 2, date '2018-07-01', 'Good' from dual
union all select 2, date '2018-07-16', 'e' from dual
union all select 2, date '2018-07-17', 'f' from dual
union all select 3, date '2018-07-01', 'g' from dual
union all select 3, date '2018-07-16', 'Good' from dual
union all select 3, date '2018-07-17', 'i' from dual
union all select 4, date '2018-07-01', 'j' from dual
union all select 4, date '2018-07-16', 'k' from dual
union all select 4, date '2018-07-17', 'Good' from dual
)
select devid, runtime, status, prev_status,
coalesce(good_status, prev_status) as best_status
from (
select devid, runtime, status,
lag(status) over (partition by devid order by runtime) as prev_status,
max(case when status = 'Good' then status end) over (partition by devid) as good_status
from (
select devid, runtime, status
from devtable
where runtime > date '2018-06-30'
)
)
where runtime = date '2018-07-17';
DEVID RUNTIME    STAT PREV BEST
---------- ---------- ---- ---- ----
1 2018-07-17 c    b    b   
2 2018-07-17 f    e    Good
3 2018-07-17 i    Good Good
4 2018-07-17 Good k    Good

您可以通过将过滤器移动到事例表达式中来删除最内部的查询：

select devid, runtime, status, prev_status,
coalesce(good_status, prev_status) as best_status
from (
select devid, runtime, status,
lag(status) over (partition by devid order by runtime) as prev_status,
max(case when runtime > date '2018-06-30' and status = 'Good' then status end)
over (partition by devid) as good_status
from devtable
)
where runtime = date '2018-07-17';

但这可能会做更多的工作，因为它会检查和计算很多你不关心的数据。

Answer 2

分析函数应该执行您想要的操作。目前还不清楚你的结果应该是什么样子，但这收集了你需要的信息：

SELECT d.*
FROM (SELECT d.*, 
LAG(d.status) OVER (PARTITION BY d.devid ORDER BY d.runtime) as prev_status,
LAG(d.runtime) OVER (PARTITION BY d.devid ORDER BY d.runtime) as prev_runtime,
ROW_NUMBER() OVER (PARTITION BY d.devid ORDER BY d.runtime) as seqnum,
SUM(CASE WHEN status = 'GOOD' THEN 1 ELSE 0 END) OVER (PARTITION BY d.devid) as num_good
FROM devtable d
WHERE d.runtime = DATE '2018-07-17' AND
d.runtime > DATE '2018-06-2018'
) d
WHERE seqnum = 1;