![]() This should work on 9.1 - not tested, but the documentation indicates that no post-9.1 stuff is being used.Ĭreated a table: ntest=# create table pv_tab(created_at timestamp, status varchar(10)) Īnd populated it. I also found the documentation on generate_series and CROSSTAB here and here respectively to be useful). (I found the following three threads helpful - 1, 2 and 3. This is in reply to request in the comments rather than to the unclear question. Note that total includes all rows not excluded before aggregation, even if filtered by crosstab(). ) AS t(status text, t0 int, t1 int, t2 int, t3 int, t4 int) , 'VALUES (0), (1), (2), (3), (-1)' - switched to VALUES for more sophisticated series ![]() GROUP BY GROUPING SETS ((1, 2), 1) - add totals per status $$ SELECT status, COALESCE(slot, -1), ct - special slot for totals To add totals per status use the new GROUPING SETS in Postgres 9.5+ SELECT status ) AS t(status text, t0 int, t1 int, t2 int, t3 int) If you need 0 instead of NULL in the result, fix with COALESCE(), but that's merely a cosmetic problem: SELECT status Every value not found on either side - not in the raw data or not generated by the 2nd parameter - is simply ignored. ![]() The second crosstab parameter ( 'SELECT generate_series(0,3)') is a query string when executed returning one row for every target column. WHERE created_at < ' 12:00' - exclude later rows Smallest first, or unexpected results will be obtainedįor regular buckets you can use another variant that's available in Postgres 9.1 as well.Ĭombine it with crosstab() re-using the same bounds as column names (the rest of the query works with Postgres 9.1): SELECT * FROM crosstab( Less than the first lower bound the thresholds array must be sorted, Return the bucket number to which operand would be assigned given anĪrray listing the lower bounds of the buckets returns 0 for an input Explanation straight from the manual: width_bucket(operand anyelement, thresholds anyarray) To be precise, it requires the variant with arbitrary bounds (since there is no regular pattern in the OP's bounds) introduced with Postgres 9.5. This fits the often overlooked function width_bucket() perfectly. The logic would be to count events that happened up to and excluding the next bound. Assuming 0:27:15 instead of 1:27:15 in the underlying table, the question would make more sense to me: CREATE TABLE tbl (created_at timestamp, status text)
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |