我有一个看法:
col1aaa aaa ccc ddd
col1
aaa
ccc
ddd
如何添加在值更改时计数的列?示例:
col1 value_change_counteraaa 1aaa 1ccc 2ddd 3
col1 value_change_counter
aaa 1
ccc 2
ddd 3
另一种情况更复杂。我不确定这是否可能。我想在col1改变时计数,但在col2改变时重置计数器示例:
col1 col2 value_change_counteraaa ttt 1aaa ttt 1bbb ttt 2ccc yyy 1ddd uuu 1eee uuu 2fff uuu 3
col1 col2 value_change_counter
aaa ttt 1
bbb ttt 2
ccc yyy 1
ddd uuu 1
eee uuu 2
fff uuu 3
5cnsuln71#
使用DENSE_RANK解析函数:
DENSE_RANK
SELECT t.*, DENSE_RANK() OVER (PARTITION BY col2 ORDER BY col1) AS value_change_counterFROM table_name t
SELECT t.*,
DENSE_RANK() OVER (PARTITION BY col2 ORDER BY col1) AS value_change_counter
FROM table_name t
其中,对于示例数据:
CREATE TABLE table_name (col1, col2) AS SELECT 'aaa', 'ttt' FROM DUAL UNION ALL SELECT 'aaa', 'ttt' FROM DUAL UNION ALL SELECT 'bbb', 'ttt' FROM DUAL UNION ALL SELECT 'ccc', 'yyy' FROM DUAL UNION ALL SELECT 'ddd', 'uuu' FROM DUAL UNION ALL SELECT 'eee', 'uuu' FROM DUAL UNION ALL SELECT 'fff', 'uuu' FROM DUAL;
CREATE TABLE table_name (col1, col2) AS
SELECT 'aaa', 'ttt' FROM DUAL UNION ALL
SELECT 'bbb', 'ttt' FROM DUAL UNION ALL
SELECT 'ccc', 'yyy' FROM DUAL UNION ALL
SELECT 'ddd', 'uuu' FROM DUAL UNION ALL
SELECT 'eee', 'uuu' FROM DUAL UNION ALL
SELECT 'fff', 'uuu' FROM DUAL;
输出:| COL1| COL 2系统|值_更改_计数器|| - -|- -|- -|| 美国汽车协会|TTT技术|一个|| 美国汽车协会|TTT技术|一个|| bbb型|TTT技术|2个|| ddd的|乌乌|一个|| 电子设备|乌乌|2个|| fff函数|乌乌|三个|| ccc格式|年|一个|fiddle
mwngjboj2#
在SQL中,结果集具有不确定的顺序(它们是无序的),因此您不能依赖于行的任何特定顺序,您需要一个单独的列来为这些行指定特定顺序(如果它不对应于按col1和/或col2的字母顺序排序)。如果有这样的列,例如:
col2
CREATE TABLE table_name (id, col1, col2) AS SELECT 1, 'aaa', 'ttt' FROM DUAL UNION ALL SELECT 2, 'aaa', 'ttt' FROM DUAL UNION ALL SELECT 3, 'bbb', 'ttt' FROM DUAL UNION ALL SELECT 4, 'ccc', 'yyy' FROM DUAL UNION ALL SELECT 5, 'ddd', 'uuu' FROM DUAL UNION ALL SELECT 6, 'eee', 'uuu' FROM DUAL UNION ALL SELECT 7, 'fff', 'uuu' FROM DUAL UNION ALL SELECT 8, 'fff', 'ttt' FROM DUAL UNION ALL SELECT 9, 'ggg', 'ttt' FROM DUAL UNION ALL SELECT 10, 'fff', 'uuu' FROM DUAL;
CREATE TABLE table_name (id, col1, col2) AS
SELECT 1, 'aaa', 'ttt' FROM DUAL UNION ALL
SELECT 2, 'aaa', 'ttt' FROM DUAL UNION ALL
SELECT 3, 'bbb', 'ttt' FROM DUAL UNION ALL
SELECT 4, 'ccc', 'yyy' FROM DUAL UNION ALL
SELECT 5, 'ddd', 'uuu' FROM DUAL UNION ALL
SELECT 6, 'eee', 'uuu' FROM DUAL UNION ALL
SELECT 7, 'fff', 'uuu' FROM DUAL UNION ALL
SELECT 8, 'fff', 'ttt' FROM DUAL UNION ALL
SELECT 9, 'ggg', 'ttt' FROM DUAL UNION ALL
SELECT 10, 'fff', 'uuu' FROM DUAL;
然后,在Oracle 12中,您可以使用MATCH_RECOGNIZE执行逐行处理并生成col2分区,然后使用第二次传递为col1生成每个分区内的排序:
MATCH_RECOGNIZE
SELECT id, col1, col2, value_change_counterFROM ( SELECT * FROM table_name MATCH_RECOGNIZE( ORDER BY id MEASURES MATCH_NUMBER() AS mno ALL ROWS PER MATCH PATTERN (same_col2+) DEFINE same_col2 AS col2 = FIRST(col2) ))MATCH_RECOGNIZE( PARTITION BY mno ORDER BY id MEASURES MATCH_NUMBER() AS value_change_counter ALL ROWS PER MATCH PATTERN (same_col1+) DEFINE same_col1 AS col1 = FIRST(col1))
SELECT id, col1, col2, value_change_counter
FROM (
SELECT *
FROM table_name
MATCH_RECOGNIZE(
ORDER BY id
MEASURES
MATCH_NUMBER() AS mno
ALL ROWS PER MATCH
PATTERN (same_col2+)
DEFINE
same_col2 AS col2 = FIRST(col2)
)
PARTITION BY mno
MATCH_NUMBER() AS value_change_counter
PATTERN (same_col1+)
same_col1 AS col1 = FIRST(col1)
输出:| 识别码|COL1| COL 2系统|值_更改_计数器|| - -|- -|- -|- -|| 一个|美国汽车协会|TTT技术|一个|| 2个|美国汽车协会|TTT技术|一个|| 三个|bbb型|TTT技术|2个|| 四个|ccc格式|年|一个|| 五个|ddd的|乌乌|一个|| 六个|电子设备|乌乌|2个|| 七个|fff函数|乌乌|三个|| 八个|fff函数|TTT技术|一个|| 九个|GGG型|TTT技术|2个|| 10个|fff函数|乌乌|一个|fiddle
2条答案
按热度按时间5cnsuln71#
使用
DENSE_RANK
解析函数:其中,对于示例数据:
输出:
| COL1| COL 2系统|值_更改_计数器|
| - -|- -|- -|
| 美国汽车协会|TTT技术|一个|
| 美国汽车协会|TTT技术|一个|
| bbb型|TTT技术|2个|
| ddd的|乌乌|一个|
| 电子设备|乌乌|2个|
| fff函数|乌乌|三个|
| ccc格式|年|一个|
fiddle
mwngjboj2#
在SQL中,结果集具有不确定的顺序(它们是无序的),因此您不能依赖于行的任何特定顺序,您需要一个单独的列来为这些行指定特定顺序(如果它不对应于按
col1
和/或col2
的字母顺序排序)。如果有这样的列,例如:
然后,在Oracle 12中,您可以使用
MATCH_RECOGNIZE
执行逐行处理并生成col2
分区,然后使用第二次传递为col1
生成每个分区内的排序:输出:
| 识别码|COL1| COL 2系统|值_更改_计数器|
| - -|- -|- -|- -|
| 一个|美国汽车协会|TTT技术|一个|
| 2个|美国汽车协会|TTT技术|一个|
| 三个|bbb型|TTT技术|2个|
| 四个|ccc格式|年|一个|
| 五个|ddd的|乌乌|一个|
| 六个|电子设备|乌乌|2个|
| 七个|fff函数|乌乌|三个|
| 八个|fff函数|TTT技术|一个|
| 九个|GGG型|TTT技术|2个|
| 10个|fff函数|乌乌|一个|
fiddle