Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
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.
Product Sales Analysis I - III
2
Demo
Update as a function result
Krug_test
PostreSQL: warehouse table
loggin_trigger-audit
revenue average per current month, last 3, 6 and 12 months
proj
select from json[]
stackse - search stackoverflow differently
Please log in to post a comment.