What you ask for is impossible. SQL is a strictly typed language. PostgreSQL functions need to declare a return type (RETURNS ..) at the time of creation. A limited way around this is with polymorphic functions. If you can provide the return type at the time of the function call. But that’s not evident from … Read more
Use crosstab() from the tablefunc module. SELECT * FROM crosstab( $$SELECT user_id, user_name, rn, email_address FROM ( SELECT u.user_id, u.user_name, e.email_address , row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST) AS rn FROM usr u LEFT JOIN email_tbl e USING (user_id) ) sub WHERE rn < 4 ORDER BY user_id $$ , … Read more
First compute the average with the aggregate function avg(): SELECT neighborhood, bedrooms, avg(price) FROM listings GROUP BY 1,2 ORDER BY 1,2; Then feed the result to the crosstab() function as instructed in great detail in this related answer: PostgreSQL Crosstab Query
The problem with your query is that b and c share the same timestamp 2012-01-02 00:00:00, and you have the timestamp column timeof first in your query, so – even though you added bold emphasis – b and c are just extra columns that fall in the same group 2012-01-02 00:00:00. Only the first (b) … Read more
If you have not installed the additional module tablefunc, run this command once per database: CREATE EXTENSION tablefunc; Answer to question A very basic crosstab solution for your case: SELECT * FROM crosstab( ‘SELECT bar, 1 AS cat, feh FROM tbl_org ORDER BY bar, feh’) AS ct (bar text, val1 int, val2 int, val3 int); … Read more
I use groupby and size df.groupby([‘id’, ‘group’, ‘term’]).size().unstack(fill_value=0) Timing 1,000,000 rows df = pd.DataFrame(dict(id=np.random.choice(100, 1000000), group=np.random.choice(20, 1000000), term=np.random.choice(10, 1000000)))
Install the additional module tablefunc once per database, which provides the function crosstab(). Since Postgres 9.1 you can use CREATE EXTENSION for that: CREATE EXTENSION IF NOT EXISTS tablefunc; Improved test case CREATE TABLE tbl ( section text , status text , ct integer — “count” is a reserved word in standard SQL ); INSERT … Read more
I’m going to add a somewhat longer and more detailed explanation of the steps to take to solve this problem. I apologize if it’s too long. I’ll start out with the base you’ve given and use it to define a couple of terms that I’ll use for the rest of this post. This will be … Read more