New answers tagged

0

It's always good to provide sample data as insert statements along with create table statements. Now, the following is completely untested: SELECT cust_id, SUM(paid_amt) as paid_amt, pd_date FROM ( SELECT cust_id, paid_amt, pd_date , RANK() OVER (PARTITION BY cust_id ORDER BY pd_date desc) AS rnk FROM T ) WHERE rnk = ...


1

I can't reproduce this problem using the same version of SSDT as you. I do have a slightly newer Visual Studio (15.9.24), but I'd be surprised if that matters. Here's my (very minimal) setup, please let me know if I've misunderstood some part of your scenario: SSDT Project clr.sql CREATE SCHEMA [clr] Stats.sql CREATE SCHEMA [Stats] StatsEntropy.cs using ...


1

You can use status to set groups, and then get min and max time of each group. If there is a serial id (PK), and it can be used to set an order, maybe you can get a better performance. Due each intermediate status=0 belongs to two groups, I've added a new column with the time of the next row to get max(time). with ev as ( select time, status, ...


1

Sounds like you want the aggregate FILTER clause: SELECT time_bucket('1 day'::interval, c.dataora) AS dataora , c.codicestazione , c.idcomune , count(c.id) AS totale_conferimenti , sum(c.peso) AS totale_peso_conferimenti , sum(c.peso) FILTER (WHERE tiporifiuto = 0) AS totale_peso_conferimento_tipo FROM conferimenti c GROUP BY 1, c....


2

Since dynamic data masking is performed right before returning the query result to the user, applying functions such as AVG will not have the masked data as a resultset. You can see this in the execution plan, the masking function appears right before the select (in other words, returning the data to the user): While the actual aggreggate happens in the ...


1

This can be solved by aggregating first and joining to the result of the aggregation: SELECT c.name as campaign_name, o.name as organization_name d.total_amount FROM ( select "campaignId", sum(amount) as total_amount from donations group by "campaignId" ) d JOIN campaigns c ON c.id = d."campaignId" ...


1

Your own solution might be suboptimal but I am not sure there is a way to solve the problem elegantly and/or efficiently without at least some redundancy. I managed to avoid hitting the customers table more than once, but I still had to reference multiqueue twice. This is the query I ended up with: SELECT q.ID, q.Content, q.Volume, q.CustomerID, ...


0

Below is a query that achieves the goal, though it might be far suboptimal: SELECT p1.ID, Content, Volume, CustomerID, runtot FROM ( SELECT ID, Content, Volume, CustomerID, SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum FROM multiqueue WHERE PublishedTS IS NULL ) ...


0

SELECT q1.ID, Content, Volume, CustomerID, runtot FROM ( SELECT ID, Content, Volume, CustomerID, SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum FROM multiqueue WHERE PublishedTS IS NULL ) AS q1 JOIN customers AS c1 ON q1.CustomerID=c1.ID WHERE runtot < 2000 *...


4

One way to do it: select extract(EPOCH from ts) - extract(EPOCH from first_value(ts) over (order by ts)) from t;


6

I think you mean “seconds”, not “minutes”, and your values are slightly off. If my suspicion is right, you could do it with the window function first_value and use extract to extract the number of seconds: SELECT *, EXTRACT(epoch FROM time - first_value(time) OVER (ORDER BY time) ) FROM mytable; id | track_seg_id | ...


0

I had the same problem and this post solved it: https://blog.faraday.io/how-to-aggregate-jsonb-in-postgres/ The idea is to create an aggregate: CREATE AGGREGATE jsonb_object_agg(jsonb) ( SFUNC = 'jsonb_concat', STYPE = jsonb, INITCOND = '{}' ); See the linked article for more details.


0

Ok, so i've got a working query that is happening in an acceptable timeframe. It feels ugly, so if there are obvious ways I can improve it please do let me know. with base_data as ( /*This is where the query for incidents/static assets goes*/ select affected_sectors, involved_parties, reported_at, tags, casualties from incidents ...


0

... but it is the other way around. The wild card would need to be in the inputted value. Yes, it's backwards, but ILIKE (internally operator ~~*) nor the alternative regexp operator ~* have commutators. I.e., you can't turn the expression around, can't switch left and right operand. See: SELECT * FROM pg_operator WHERE oprname IN ('~~*', '~*'); oprcom =...


Top 50 recent answers are included