Get a count of consecutive days that match specified criteria
I have the following structure in my Oracle database:
Date Allocation id
2015-01-01 Same 200
2015-01-02 Good 200
2015-01-03 Same 200
2015-01-04 Same 200
2015-01-05 Same 200
2015-01-06 Good 200
I would like to have a query that should only check the previous consecutive days and get a counter where Allocation is "Same"
.
I want to select by date, for example 2015-01-05
.
Output example: 2015-01-05
counter for date 3
.
New problem. With a request from Lukas Eder always 1
or 2
. but expected 3
. What for?!
Date Allocation id
2015-01-01 Same 400
2015-01-02 Good 400
2015-01-03 Same 400
2015-01-04 Same 400
2015-01-05 Same 400
2015-01-06 Good 400
Code from Lucas Eder
SELECT c
FROM (
SELECT allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS c
FROM (
SELECT allocation, d,
d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part
FROM t
)
)
WHERE d = DATE '2015-01-05';
The expected result looks like this: First_day end The last day is not needed:
id count first_day Last_Day
200 3 2015-01-03 2015-01-05
400 3 2015-01-03 2015-01-05
source to share
This query will count the values for each row:
SELECT allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS c
FROM (
SELECT allocation, d,
d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part
FROM t
)
ORDER BY d;
Then you can filter it to find the counts for the given row:
SELECT c
FROM (
SELECT allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS c
FROM (
SELECT allocation, d,
d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part
FROM t
)
)
WHERE d = DATE '2015-01-05';
Explanation:
The resulting table is used to calculate different "partitions" part
for each date and distribution:
SELECT allocation, d,
d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part
FROM t
Result:
allocation d part
--------------------------------
Same 01.01.15 31.12.14
Good 02.01.15 01.01.15
Same 03.01.15 01.01.15
Same 04.01.15 01.01.15
Same 05.01.15 01.01.15
Good 06.01.15 04.01.15
The specific date created part
is irrelevant. It's just some kind of date that will be the same for every "group" of dates within the selection. Then you can count the number of identical values (allocation, part)
using the window function count(*) over(...)
:
SELECT allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS c
FROM (...)
ORDER BY d;
to get the desired result.
Data
I used the following table as an example:
CREATE TABLE t AS (
SELECT DATE '2015-01-01' AS d, 'Same' AS allocation FROM dual UNION ALL
SELECT DATE '2015-01-02' AS d, 'Good' AS allocation FROM dual UNION ALL
SELECT DATE '2015-01-03' AS d, 'Same' AS allocation FROM dual UNION ALL
SELECT DATE '2015-01-04' AS d, 'Same' AS allocation FROM dual UNION ALL
SELECT DATE '2015-01-05' AS d, 'Same' AS allocation FROM dual UNION ALL
SELECT DATE '2015-01-06' AS d, 'Good' AS allocation FROM dual
);
source to share
To solve your problem, consider the following query:
SELECT COUNT(*) AS `count` FROM test t
WHERE `date` < '2015-01-05' AND allocation = 'Same';
Let's assume the given date is "2015-01-05". The idea here is to select all dates that are less than "2015-01-05", which means its previous days. Since the allocation must be "the same", it is also included in the conditions section of the instruction.
source to share