Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
PostgreSQL pivot using temp view
-- Test data create table t(datetime timestamptz, entity char(1), result bool); insert into t values ('2016-01-01 11:00:01', 'a', true), ('2016-01-01 17:00:01', 'a', true), -- two events for :a on same day ('2016-01-01 11:01:01', 'b', false), ('2016-01-01 11:03:01', 'c', true), ('2016-01-01 13:00:01', 'd', true), -- only one event for :d ('2016-01-02 11:00:01', 'a', true), ('2016-01-02 11:01:01', 'b', false), ('2016-01-02 11:03:01', 'c', true); do $$ -- Here we will create the view to select desired data declare select_clause text := 'entity'; dates date[]; d date; date_filter text; begin -- Generate array with dates for our columns select array_agg(dt) into dates from generate_series((select min(datetime) from t)::date, (select max(datetime) from t)::date, '1 day') as dt; raise info '%', dates; -- Generate "select part" foreach d in array dates loop date_filter := format('datetime::date = %L', d); raise notice '%', date_filter; select_clause := select_clause || ', array[count(*) filter(where ' || date_filter || '), count(*) filter(where ' || date_filter || ' and result), count(*) filter(where ' || date_filter || ' and not result)] as ' || quote_ident(d::text); end loop; raise info '%', select_clause; -- Create temporary view using previousely generated "select part" -- "temp view" is session-wide execute 'create or replace temp view v as select ' || select_clause || ' from t group by entity'; end $$; select * from v order by entity;
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
3c
Black Wall
project 1
PostreSQL: product table
Postgres CrossTab for query with variable number of columns
Query integer in text field - Seemingly doesn't work as expected
Assignment 1(SET A)
Movie Rating SQL Analysis Project
postgresql timestamp
SQL social network practice by Han Wang 20200720 - 2
Please log in to post a comment.