DerBenniAusA
Reputation: 817
SQL query for counting changes of values in a column
Given the following table structure:
| CAT | NR | DATE | VERSION | SOME_CODE |
|---|---|---|---|---|
| ABC | 123 | 2009-02-19T00:00:00Z | 1 | OPP |
| ABC | 456 | 2009-03-18T00:00:00Z | 1 | ZUM |
| ABC | 444 | 2009-03-18T00:00:00Z | 1 | ZUM |
| ABC | 456 | 2009-04-18T00:00:00Z | 2 | XXX |
| ABC | 456 | 2009-04-18T00:00:00Z | 3 | XXX |
| ABC | 456 | 2009-04-18T00:00:00Z | 4 | UIO |
| ABC | 456 | 2009-05-18T00:00:00Z | 5 | RQA |
| ABC | 444 | 2009-03-18T00:00:00Z | 2 | MUZ |
| DEF | 789 | 2018-02-17T00:00:00Z | 1 | WER |
| DEF | 637 | 2018-02-16T00:00:00Z | 1 | FAW |
| GHI | 248 | 2018-02-17T00:00:00Z | 1 | QWE |
| GHI | 248 | 2019-02-17T00:00:00Z | 2 | PPP |
| GHI | 357 | 2020-02-16T00:00:00Z | 1 | FFF |
| GHI | 357 | 2020-02-16T00:00:00Z | 2 | GGG |
| GHI | 357 | 2020-02-16T00:00:00Z | 3 | LLL |
| GHI | 357 | 2020-02-16T00:00:00Z | 4 | LLL |
| GHI | 357 | 2020-08-16T00:00:00Z | 4 | FFF |
| GHI | 357 | 2020-10-16T00:00:00Z | 5 | ZZZ |
| GHI | 420 | 2020-02-16T00:00:00Z | 1 | QDS |
I need to find out how many effective changes took place in column SOME_CODE (sometimes values are the same and therefore they should not count) per month and category (CAT).
So expected SQL query result should like this:
| CAT | MONTH | SOME_CODE_CHANGES |
|---|---|---|
| ABC | APR 2009 | 1 |
| ABC | MAR 2009 | 1 |
| GHI | FEB 2020 | 2 |
Does anybody have a hint how to start the query?
Within a category (e.g. ABC) I want to count number of changes in column SOME_CODE. When a changed happened (e.g. for NR=456) say from VERSION 1 to 2 ("ZUM" -> "XXX") I wanna count it but with two conditions:
- must be a change within a month
- must be an effective change. Means the change from VERSION 3 to VERSION 4 is a valid change ("XXX" -> "UIO") while the change from VERSION 2 to VERSION 3 is nonesense ("XXX" -> "XXX") and should not be counted
My approach:
select
trunc(DATE, 'MON'),
CAT,
sum(case when _MY_CONDITION_ then 1 else 0 end) counter
from table where date between TO_TIMESTAMP('01/06/2018 00:00:00.000000000', 'DD/MM/YYYY HH24:MI:SS.FF') and TO_TIMESTAMP('31/07/2018 00:00:00.000000000', 'DD/MM/YYYY HH24:MI:SS.FF')
group by
trunc(DATE, 'MON'),
CAT
order by
trunc(DATE, 'MON')
Upvotes: 0
Views: 709
Answers (2)
MT0
Reputation: 168623
From Oracle 12, you can use MATCH_RECOGNIZE:
SELECT cat,
month,
COUNT(*)
FROM (
SELECT t.*,
TRUNC( "DATE", 'MM' ) AS month
FROM table_name t
)
MATCH_RECOGNIZE(
PARTITION BY cat, month
ORDER BY "DATE", version
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST change_code
PATTERN ( strt change_code )
DEFINE
change_code AS change_code.some_code <> strt.some_code
)
GROUP BY cat, month
Which, for the sample data:
CREATE TABLE table_name ( CAT, NR, "DATE", VERSION, SOME_CODE ) AS
SELECT 'ABC', 123, TIMESTAMP '2009-02-19 00:00:00 UTC', 1, 'OPP' FROM DUAL UNION ALL
SELECT 'ABC', 456, TIMESTAMP '2009-03-18 00:00:00 UTC', 1, 'ZUM' FROM DUAL UNION ALL
SELECT 'ABC', 444, TIMESTAMP '2009-03-18 00:00:00 UTC', 1, 'ZUM' FROM DUAL UNION ALL
SELECT 'ABC', 444, TIMESTAMP '2009-03-18 00:00:00 UTC', 2, 'MUZ' FROM DUAL UNION ALL
SELECT 'ABC', 456, TIMESTAMP '2009-04-18 00:00:00 UTC', 2, 'XXX' FROM DUAL UNION ALL
SELECT 'ABC', 456, TIMESTAMP '2009-04-18 00:00:00 UTC', 3, 'XXX' FROM DUAL UNION ALL
SELECT 'ABC', 456, TIMESTAMP '2009-04-18 00:00:00 UTC', 4, 'UIO' FROM DUAL UNION ALL
SELECT 'ABC', 456, TIMESTAMP '2009-05-18 00:00:00 UTC', 5, 'RQA' FROM DUAL UNION ALL
SELECT 'DEF', 637, TIMESTAMP '2018-02-16 00:00:00 UTC', 1, 'FAW' FROM DUAL UNION ALL
SELECT 'DEF', 789, TIMESTAMP '2018-02-17 00:00:00 UTC', 1, 'WER' FROM DUAL UNION ALL
SELECT 'GHI', 248, TIMESTAMP '2018-02-17 00:00:00 UTC', 1, 'QWE' FROM DUAL UNION ALL
SELECT 'GHI', 248, TIMESTAMP '2019-02-17 00:00:00 UTC', 2, 'PPP' FROM DUAL UNION ALL
SELECT 'GHI', 357, TIMESTAMP '2020-02-16 00:00:00 UTC', 1, 'FFF' FROM DUAL UNION ALL
SELECT 'GHI', 420, TIMESTAMP '2020-02-16 00:00:00 UTC', 1, 'QDS' FROM DUAL UNION ALL
SELECT 'GHI', 357, TIMESTAMP '2020-02-16 00:00:00 UTC', 2, 'GGG' FROM DUAL UNION ALL
SELECT 'GHI', 357, TIMESTAMP '2020-02-16 00:00:00 UTC', 3, 'LLL' FROM DUAL UNION ALL
SELECT 'GHI', 357, TIMESTAMP '2020-02-16 00:00:00 UTC', 4, 'LLL' FROM DUAL UNION ALL
SELECT 'GHI', 357, TIMESTAMP '2020-08-16 00:00:00 UTC', 4, 'FFF' FROM DUAL UNION ALL
SELECT 'GHI', 357, TIMESTAMP '2020-10-16 00:00:00 UTC', 5, 'ZZZ' FROM DUAL
Outputs:
CAT | MONTH | COUNT(*) :-- | :-------- | -------: ABC | 01-MAR-09 | 1 ABC | 01-APR-09 | 1 DEF | 01-FEB-18 | 1 GHI | 01-FEB-20 | 3
If you want to see the changes then you can use:
SELECT *
FROM (
SELECT t.*,
TRUNC( "DATE", 'MM' ) AS month
FROM table_name t
)
MATCH_RECOGNIZE(
PARTITION BY cat, month
ORDER BY "DATE", version
MEASURES
MATCH_NUMBER() AS mn,
FIRST( some_code ) AS change_from,
LAST( some_code ) AS change_to
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST change_code
PATTERN ( strt change_code )
DEFINE
change_code AS change_code.some_code <> strt.some_code
)
Which outputs:
CAT | MONTH | MN | CHANGE_FROM | CHANGE_TO :-- | :-------- | -: | :---------- | :-------- ABC | 01-MAR-09 | 1 | ZUM | MUZ ABC | 01-APR-09 | 1 | XXX | UIO DEF | 01-FEB-18 | 1 | FAW | WER GHI | 01-FEB-20 | 1 | FFF | QDS GHI | 01-FEB-20 | 2 | QDS | GGG GHI | 01-FEB-20 | 3 | GGG | LLL
db<>fiddle here
If your requirement for "within a month" is that you want changes where there is at most one month's difference between the previous row to the changed row, even if the rows are in two different calendar months, (rather than just the changes that happen in the same calendar month) then you can use:
SELECT cat,
TRUNC( change_date, 'MM' ) AS month,
COUNT(*)
FROM table_name
MATCH_RECOGNIZE(
PARTITION BY cat
ORDER BY "DATE", version
MEASURES
LAST( "DATE" ) AS change_date
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST change_code
PATTERN ( strt change_code )
DEFINE
change_code AS (
change_code.some_code <> strt.some_code
AND MONTHS_BETWEEN( change_code."DATE", strt."DATE" ) <= 1
)
)
GROUP BY cat, TRUNC( change_date, 'MM' )
Which outputs:
CAT | MONTH | COUNT(*) :-- | :-------- | -------: ABC | 01-MAR-09 | 2 ABC | 01-MAY-09 | 1 ABC | 01-APR-09 | 2 DEF | 01-FEB-18 | 1 GHI | 01-FEB-20 | 3
db<>fiddle here
Upvotes: 2
Gordon Linoff
Reputation: 1270893
I think you want one less than the number of distinct codes for each cat in each month:
select cat, trunc(date, 'MON') as yyyymm,
count(distinct code) - 1
from t
group by cat, trunc(date, 'MON');
From what I can tell, this matches the results you specify.
Upvotes: 0
Related Questions
- query to retrieve changes in multiple columns in sql
- SQL. Counting Changes in Column per group
- List The Rows Where a Column Value Changes
- SQL Select counter when values change
- sql oracle how to count value increases in column
- oracle identify every change of a column
- Oracle SQL: Applying count to changing column value
- How to query the number of changes that have been made to a particular column in SQL
- SQL - Count number of changes in an ordered list
- A query which shows changes of a column from a table