Window and CTE
PostgreSQL offers writing of Common Table Expressions (CTE) . Common Table Expressions are useful to define temporary SQL query used in a bigger query.
For this example, let's assume we want to count the new user creation per day during the month of September. One way would be to Group by
EXTRACT('day' FROM created_at) , but days without new user will return not rows, where we want it to return zero.In this case, using joins onto a generated series of day is the way to go. CTE makes it very simple to write and manage:
dates_in_september = Clear::SQL.select({
day_start: "generate_series(date '2018-09-01', date '2018-09-30', '1 day'::interval)",
day_end: "generate_series(date '2018-09-01', date '2018-09-30', '1 day'::interval) + '1 day'::interval";
})
Clear::SQL.select({
count: "COUNT(users.*)",
day: "dates.day_start"
})
.with_cte(dates: dates_in_septembers)
.from("dates")
.left_joins(User.table){ (users.created_at >= day_start) & (users.created_at < day_end) }
.group_by("dates.day_start")
.order_by("dates.day_start")
.fetch do |hash|
puts "users created the #{hash["day"]}: #{hash["count"]}"
end
Since all model collections are SQL query, you can pass collection as parameter of
with_cte block.Clear::SQL
.select("sum(salary) OVER w", "avg(salary) OVER w")
.from("empsalary")
.window({w: "(PARTITION BY depname ORDER BY salary DESC)"})
Last modified 3yr ago