SQL查询应该计算不同的条目，但在相互跟踪时会过滤掉重复的条目

Question

给定如下表结构：

版本SOME_CODE<1>OPPZUMZUMRQA一汽<1>WER[/tr>2018-02-16 00:00:00BBBAAA2018-02-19 00:00:002018-02-20 00:00:00AAA2018-02-21 00:00:002018-02-22 00:00:002018-02-23 00:00:002018-02-24 00:00:002018-02-25 00:00:002018-02-26 00:00:002018-02-26 00:00:002018-02-27 00:00:002018-02-17 00:00:00>购买力平价法国队QDSGGGLLL>td>法国队ZZZ

CAT	BUSINES_NR	TIME_A
ABC	123	2009-02-19T00:00:00
ABC	456	2009-03-18 00:00:00	1
ABC	444	2009-03-18 00:00:00	1
ABC	444	2009-03-18 00:00:00		2	MUZ
ABC	456	2009-04-18 00:00:00		2	XXX
ABC	456	2009-04-18 00:00:00		3	XXX
ABC	456	2009-04-18 00:00:00		UIO
ABC	456	2009-05-18 00:00:00		5
DEF	637	2018-02-16 00:00:00	1
DEF	789	2018-02-17 00:00:00
SPZ	123	1	AAA
SPZ	123	2018-02-17 00:00:00	2
SPZ	123	2018-02-18 00:00:00	3
SPZ	123			CCC
SPZ	123		5
SPZ	123			DDD
SPZ	123			DDD
SPZ	123			DDD
SPZ	123			EEE
SPZ	123	10	EEE
SPZ	123	11	DDD
SPZ	123	2018-02-27 00:00:00	12	BBB
SPZ	123	13	EEE
SPZ	123	14	EEE
GHI	248	1	QWE
GHI	248	2019-02-17 00:00:00	2
GHI	357	2020-02-16 00:00:00	1
GHI	420	2020-02-16 00:00:00	1
GHI	357	2020-02-16 00:00:00	2
GHI	357	2020-02-16 00:00:00	3
GHI	357	2020-02-16 00:00:00		LLL
GHI	357	2020-08-16 00:00:00	4
GHI	357	2020年10月16日00:00:00	5

您可以使用LAG函数查看以前的版本，看看some_code是否与当前版本匹配，然后使用SUM来获得所有代码更改的总数。

SELECT cat, moenat, SUM (version_change) AS total_version_changes
FROM (SELECT cat,
TRUNC (TIME_A, 'MON')    AS moenat,
CASE
WHEN    some_code <>
LAG (some_code) OVER (PARTITION BY cat ORDER BY version, time_a)
OR LAG (some_code) OVER (PARTITION BY cat ORDER BY version, time_a) IS NULL --Needed for version 1
THEN
1
ELSE
0
END                      AS version_change
FROM blah)
GROUP BY cat, moenat
ORDER BY cat, moenat;
CAT       MOENAT    TOTAL_VERSION_CHANGES
______ ____________ ________________________
ABC    01-FEB-09                           1
ABC    01-MAR-09                           2
ABC    01-APR-09                           2
ABC    01-MAY-09                           1
DEF    01-FEB-18                           2
GHI    01-FEB-18                           1
GHI    01-FEB-19                           1
GHI    01-FEB-20                           4
GHI    01-AUG-20                           1
GHI    01-OCT-20                           1
SPZ    01-FEB-18                          10