CODEWITHSUNDEEP
sqloracle-databasegrouping
Vadi
Vadi

Reputation: 33

Oracle - Count of number of days a value has changed

I have a requirement to write a query in Oracle as shown in the screenshots below. Any help is greatly appreciated. Thanks a lot in advance. Vadi.

enter image description here

enter image description here

Table with sample data:

CREATE TABLE fee_check (
  trans_date DATE,
  fee1 NUMBER(6,3),
  fee2 NUMBER(6,3)
);

INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('18/04/2022','dd/mm/yyyy'), 0.74, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('19/04/2022','dd/mm/yyyy'), 0.75, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('20/04/2022','dd/mm/yyyy'), 0.75, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('21/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('22/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('23/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('24/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('25/04/2022','dd/mm/yyyy'), 0.76, 0.87);
COMMIT;

Upvotes: 0

Views: 67

Answers (2)

MT0
MT0

Reputation: 168671

If you want to only count consecutive rows (rather than aggregating non-consecutive groups with the same fee values together) then, from Oracle 12, you can use MATCH_RECOGNIZE to do row-by-row processing:

SELECT *
FROM   fee_check
MATCH_RECOGNIZE(
  ORDER BY trans_date
  MEASURES
    FIRST(trans_date) AS trans_date,
    COUNT(trans_date) AS day_count,
    FIRST(fee1) AS fee1,
    FIRST(fee2) AS fee2
  PATTERN (same_fees+)
  DEFINE same_fees AS fee1 = FIRST(fee1) AND fee2 = FIRST(fee2)
)

Or, in earlier versions, you can use analytic functions:

SELECT MIN(trans_date) AS trans_date,
       COUNT(*) AS day_count,
       MIN(fee1) AS fee1,
       MIN(fee2) AS fee2
FROM   (
  SELECT f.*,
         ROW_NUMBER() OVER (ORDER BY trans_date) -
           ROW_NUMBER() OVER (PARTITION BY fee1, fee2 ORDER BY trans_date) AS grp
  FROM   fee_check f
)
GROUP BY grp

Which, for the sample data:

CREATE TABLE fee_check (trans_date, fee1, fee2) AS
SELECT DATE '2022-04-18', 0.74, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-19', 0.75, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-20', 0.75, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-21', 0.73, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-22', 0.73, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-23', 0.73, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-24', 0.73, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-25', 0.76, 0.87 FROM DUAL UNION ALL
SELECT DATE '2022-04-26', 0.75, 0.87 FROM DUAL;

Note: there is an extra row added at the end that has the same fee1 and fee2 values earlier in the data set.

Both output:

TRANS_DATE DAY_COUNT FEE1 FEE2
2022-04-18 00:00:00 1 .74 .87
2022-04-19 00:00:00 2 .75 .87
2022-04-21 00:00:00 4 .73 .87
2022-04-25 00:00:00 1 .76 .87
2022-04-26 00:00:00 1 .75 .87

db<>fiddle here

Upvotes: 1

ManthaN N Patel
ManthaN N Patel

Reputation: 92

This is the solution of your problem

SELECT MIN(trans_date) trans_date, COUNT(*) DayCount, fee1, fee2 
FROM fee_check 
GROUP BY fee1,fee2
ORDER BY trans_date

Upvotes: 1

Related Questions