如何计算Oracle SQL中的更改 [英] How to calculate changes in Oracle sql
本文介绍了如何计算Oracle SQL中的更改的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下表,其中包含以下列:
I have the following table with the following columns:
HID_1 HID_2 Attr1 Attr2 Attr3 Attr4 Attr5
123 111 wo e ak ERR 20180630
123 111 wo e ak ERR 20180730
123 111 wo e ak ERR 20180830
123 111 qe e ak ERR 20180930
123 111 qe e ak ERR 20181030
123 111 aa a ak ERR 20181130
其中HID_1和HID_2是哈希ID广告,另外4列由group by语句定义,最后一个列是time_id(该月最后一天的日期).总的来说,在此表中,我有很多具有不同HID的记录.
Where HID_1 and HID_2 are hash-id ad other 4 columns are defined by the group by statement and the last one is time_id(date of the last day of the month). In general in this table I have much more records with a lot of different HID.
我想将HID_2的许多更改(在Attr1-Attr4中)作为单独的列. 根据第一个示例,答案应该是这样的:
I want to coumpute a number of changes(in Attr1 - Attr4) for the HID_2 as separate column. Based on the first example the answer should be like this:
HID_1 HID_2 Attr1 Attr2 Attr3 Attr4 Attr5 Attr6
123 111 wo e ak ERR 20180630 0
123 111 wo e ak ERR 20180730 0
123 111 wo e ak ERR 20180830 0
123 111 qe e ak ERR 20180930 1
123 111 qe e ak ERR 20181030 0
123 111 aa a ak ERR 20181130 2
如何在Oracle sql数据库中进行操作?
How can I do in Oracle sql Database?
推荐答案
尝试一下:
select t.*
, case when attr1 != LAG(attr1, 1, attr1) OVER (PARTITION BY hid_1, hid_2 ORDER BY attr5) then 1 else 0 end +
case when attr2 != LAG(attr2, 1, attr2) OVER (PARTITION BY hid_1, hid_2 ORDER BY attr5) then 1 else 0 end +
case when attr3 != LAG(attr3, 1, attr3) OVER (PARTITION BY hid_1, hid_2 ORDER BY attr5) then 1 else 0 end +
case when attr4 != LAG(attr4, 1, attr4) OVER (PARTITION BY hid_1, hid_2 ORDER BY attr5) then 1 else 0 end as attr6
from t
这篇关于如何计算Oracle SQL中的更改的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文