5

I can't speak for the query accessing the view but the samples you provided give a quick insight into the performance of the view. You are getting a filter operator at the end of all of your joins (INNER & LEFT OUTER) to be able to satisfy the where clause: WHERE (matter.sid IS NULL AND data.matterId = '') OR matter.sid IS NOT NULL Splitting the view up ...


4

SELECT d.name, b.breeds_text FROM dogs d CROSS JOIN LATERAL ( SELECT ARRAY(SELECT b.breed FROM unnest(breeds) a(guid) JOIN breeds b USING (guid)) AS breeds_text ) b; Because we JOIN after unnnest(), the order of elements is not necessarily preserved. I had LEFT JOIN LATERAL (...) ON true at first. But since the ...


4

To resolve this, I did the following. All the code below is available on the fiddle here. I took another look at this and realised that my first answer (see edit history) had merely "handed you a fish", rather than "teaching you how to fish(*)"! Overview: You have two issues here, one easy enough to spot and deal with - aggregation - in ...


3

Hopefully this should provide an answser: This implements "common" relational division: SELECT DISTINCT grpid FROM groups AS g GROUP BY grpid, changeDate HAVING NOT EXISTS ( SELECT 1 FROM groups AS gi WHERE gi.grpid = g.grpid AND gi.changeDate = g.changeDate AND NOT EXISTS ( SELECT 1 FROM ...


3

Imagine you keep track of all your purchases, from chocolate bars to houses and cars, over your entire lifetime. Maybe you buy 5,000 chocolate bars at prices varying between $1 each and $2 each. For an average price of, for the sake of this example, $1.47. Then you buy a car, priced at $40,000. Now, the average price of the chocolate bars hasn't changed, ...


2

date_tunc() is the key. Transactions per hour SELECT date_trunc('hour', datetime) AS hour , count(*) AS transactons FROM tbl GROUP BY 1 ORDER BY 1; Average transactions per hour ... for weekday and weekend, respectively. SELECT extract('ISODOW' from hour)::int/6 AS weekday_weekend , round(avg(transactions), 2) AS avg_transactions FROM ( ...


2

Starting point, mostly unchanged: SELECT count(*), min(last_updated_date), max(last_updated_date) FROM schema.table; Be aware of (not) case sensitive behavior of identifiers in Postgres. If in doubt use legal, unquoted, lower-case names exclusively. See: Are PostgreSQL column names case-sensitive? A table can optionally be schema-qualified (schema.table)...


2

As Lennart mentioned, you'll need to CROSS JOIN your generated series with your users to create the required combinations of (Login,Date): SELECT U.Login ,t.Date::date AS Day ,COUNT(UserItem.UserId) AS Count ,AVG(COUNT(UserItem.UserId)) OVER (PARTITION BY U.Login ORDER By t.Date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS RollingAvg FROM "User&...


2

SELECT DISTINCT StaffCode, FIRST_VALUE(FirstName) OVER (PARTITION BY StaffCode ORDER BY CASE WHEN FirstName IS NOT NULL THEN EffectiveDate END DESC) FirstName, FIRST_VALUE(LastName) OVER (PARTITION BY StaffCode ORDER ...


2

You can use a inner join for that Fyi mysql 8 duesn't like groups as table name, and must be put in backticks And lines id = 2 and and id = 4, are also "identical, and fall under your rule create table groups ( id int, grpid int , changeDate date, userid int, pct double , hobby int ); insert into groups(id , grpid , changeDate ,userid ,pct ,...


2

I think you're looking for: SELECT n_brand, population, SUM(actions_completed_by_unique_user) as actions FROM agg_table WHERE n_brand = 'pepsico' GROUP BY n_brand, population There's no need for actions_completed_by_unique_user > 0 unless you have negative values. I also assume that n_brand is the same as platform.


1

We can get the number of connections active for each trunk/period/interval combination like so: WITH Numbers(val) AS ( SELECT 1 UNION ALL SELECT val + 1 FROM numbers WHERE val < 48 ) ,ConnectionPeriod AS ( SELECT period.period_start ,period.period_end ,@period as Period ,src.Trunk ,CASE WHEN ...


1

A possible solution can be : The first select gets all combination of date and error combination that exist in the tables. If you actually want all possible Dates, you need to add another created table, with all possiible dates CREATE TABLE Code_SKU_Combos ([ErrorCode] varchar(5), [Device] varchar(2)) ; INSERT INTO Code_SKU_Combos ([ErrorCode],...


1

Make it short and simple: db.reportgraphs.aggregate([ { $match:{ d:{$gte:new Date("2001-01-01T00:00:00.000Z"), $lt:new Date()}, bId: ObjectId("5fc151b2c123574e0f835da4") } }, { $set: { yr:{"$year":"$d"}, mt:{"$month":"$d"}, dy:{"$dayOfMonth":"$d"}, h:...


1

You need dynamic SQL as has been suggested. But when passing anything but values (including object names like the function name in your example), you need to defend against SQL injection! CREATE OR REPLACE PROCEDURE safe_proc(func text) LANGUAGE plpgsql AS $proc$ BEGIN DROP TABLE IF EXISTS pg_temp.foo; -- ② also safer EXECUTE format( ...


1

You have to use dymnamc sql for that purpose CREATE PROCEDURE test(func TEXT) LANGUAGE PLPGSQL AS $$ BEGIN DROP TABLE IF EXISTS foo; EXECUTE 'CREATE TEMPORARY TABLE foo AS SELECT' || func ||'(bar.column_a) as func_column_a FROM bar;'; END $$;


1

you are missing on {condition} for your last join : however I'm not sure select images.*, unit_id from images join images_units on images.id = images_units.image_id join images_sites on images.id = images_sites.image_id join ( select min(sort) as minSort, unit_id as matchID from images join images_units on images.id=...


1

One of the neat things with SQL is that it is closed under relational operators. I.e the result of a query is a derived table that you can query again. So you just apply avg to the result of your count query. In addition, you can get both the counts and the average in a single query by using grouping sets. I'm using group by cube, since there is only one ...


1

Using a sub-select query you can apply the AVG function on top of your COUNT query to get the single average like so: SELECT AVG(InvoiceCounts.total_invoices) FROM ( SELECT COUNT (h_id) AS total_invoices FROM maintenance GROUP BY house_id ) InvoiceCounts


1

It is normal that retrieving a sorted result is more effort. If you want the total count, all rows have to be scanned, even if you only return the first 50. You could try order by name || '' to see if a sort would be faster. My recommendation is not to retrieve the total count together with the query. The optimizer's estimates are good in this case, so you ...


1

Basically you cannot GROUP BY the type, because you want the SUM of both types SELECT itemcode, batchno, eXpirydate, SUM(CASE WHEN type= 1 THEN qty WHEN type= 2 THEN qty*-1 END) AS balance FROM strans GROUP BY itemcode, batchno, EXpirydate


1

nbk has answered already, I have set up your tables in a SQL fiddle, this makes it easier to play around. Consider using this tool for any more questions, because that makes it easier to talk about table structure etc. http://sqlfiddle.com/#!9/45220f/2 To elaborate more on the question/answer already given: You need to group by a column, so that all columns ...


1

Something like this: with ta as (select a.customerid, sum(a.items) amount_a from orders a group by a.customerid ), tb as (select sum(b.items) amount_b from tableb b ) select ta.customerid, ta.amount_a, tb.amount_b from ta join tb on ta.amount_a = tb.amount_b


1

In an aggregate query, every select'ed column must either be included in the "group by" clause or be wrapped in an aggregating function. You cannot have any 'select'ed column not in either of these states. Why? How would Postgres (or any other, sensible, DBMS for that matter) know which value to give you? Consider this example: select a, b, c, ...


Only top voted, non community-wiki answers of a minimum length are eligible