With help from @forpas I have this query: dbfiddle
The column ROWdifferencesALWAYS counts the amount of rows between the last row and the last time it occurred. Totally independent from the column 'date'.
I added the column ROWdifferencesMAXDATE, because I would also like to know the the amount of rows between the last row and the last time it occurred ONLY for the MAX date (2022-01-14 in this case). For this column, not all possible values in the column 'number' do exist. In that case the ROWdifferencesMAXDATE should be the amount of rows with this MAX(date) (in this case 3).
The other columns shouldn't be affected by this change. I have tried with WHERE MAX(date) etc, but then I get less records returned. Is this in a way possible?
Thank you so much in advance!
Expected outcome for this new column:
| number | ROWdifferencesMAXDATE |
|---|---|
| 3 | 3 |
| 12 | 1 |
| 14 | 0 |
| 25 | 3 |
| 38 | 3 |
| 41 | 2 |
| 73 | 3 |
CodePudding user response：
You can extend the answer to your previous question by adding a field which numbers the rows for each date, then when the number is present on that date you compute its position from that row number and the total number of rows on the maximum date value; otherwise its position is just the total number of rows on the maximum date.
WITH gdata AS (
SELECT MAX(date) AS maxdate,
COUNT(*) AS numrows
FROM numbers
),
rowdata AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY date, id) AS rn,
ROW_NUMBER() OVER (PARTITION BY date ORDER BY id) AS drn,
SUM(date = maxdate) OVER () AS numdate
FROM numbers
CROSS JOIN gdata
)
SELECT number,
COUNT(*) AS occurrences,
MIN(numrows - rn) AS ROWdifferencesALWAYS,
MIN(CASE WHEN date = maxdate THEN numdate - drn
ELSE numdate
END) AS ROWdifferencesMAXDATE
FROM rowdata
GROUP BY number
ORDER BY number
Output:
number occurrences ROWdifferencesALWAYS ROWdifferencesMAXDATE
3 1 5 3
12 1 1 1
14 1 0 0
25 1 7 3
38 1 3 3
41 2 2 2
73 1 4 3