I have a statistical table in PostgreSQL table:
开发者_Python百科article_id | date | read_count
1 | 2011-06-02 | 12
1 | 2011-06-03 | 54
1 | 2011-06-04 | 2
1 | 2011-06-05 | 432
And as i need this data in a chart, i need to get values in each week.
I know Postgres has a row_number() function, anyway i didn't get it to work properly.
SELECT "date", "read_count" FROM "articles_stats"
WHERE row_number() OVER (ORDER BY "date" ASC) % 7 = 0
ORDER BY "date" ASC
ERROR: window functions not allowed in WHERE clause
From @pst's link,
"If there is a need to filter or group rows after the window calculations are performed, you can use a sub-select."
For example:
SELECT *
FROM (
SELECT "date", "read_count",
row_number() OVER (ORDER BY "date" ASC) as n
FROM "articles_stats"
) x
WHERE x.n % 7 = 0
ORDER BY x."date" ASC
精彩评论