New answers tagged

2

As Lennart mentioned, you'll need to CROSS JOIN your generated series with your users to create the required combinations of (Login,Date): SELECT U.Login ,t.Date::date AS Day ,COUNT(UserItem.UserId) AS Count ,AVG(COUNT(UserItem.UserId)) OVER (PARTITION BY U.Login ORDER By t.Date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS RollingAvg FROM "User&...


0

I dont think you need the generate_series part if you use range instead of rows: SELECT d::date AS day, updates.login as login, avg(coalesce(updates.counts, 0)) over (order by to_char(d, 'J') RANGE between 3 preceding and 3 following) as rollingavg from ( SELECT date(item.updated_at) AS day, u.login, count(date(item....


3

Window functions work on the result after the WHERE clause is applied. Since id is unique and you ask for exactly one id, that result has a maximum of one row. There just isn't any previous row whatsoever. Further more relational tables represent relations which are a special form of (multi) sets. And (multi) sets aren't ordered. Unless there's an explicit ...


2

You just need to add an OVER and ORDER BY clause to your lag function so it knows how to order the records to select the previous one. Also you can specify how many rows back to look (in this case one row back). For example: SELECT LAG(id, 1) OVER (ORDER BY id) FROM cb_cat WHERE id=450 Using this you can either apply the LAG() function to every column you ...


Top 50 recent answers are included