Monday, March 25

Elein’s PlanetPostgreSQL

Having Group By Clauses — elein’s GeneralBits

Elein's PlanetPostgreSQL
Some people go to great lengths to avoid GROUP BY and HAVING clauses in their queries. The error messages are fussy but they are usually right. GROUP BY and HAVING key words are essential for good SQL reporting. The primary reason for GROUP BY is to reduce the number of rows, usually by aggregation. It produces only one row for each matching grouping from the input. This allows you to make sophisticated calculations via ordinary SQL. Fruit Example: We have some fruit: item | source | amt | fresh_until ---------+---------+-----+------------ bananas | Chile | 50 | 2019-05-01 bananas | Bolivia | 25 | 2019-04-15 bananas | Chile | 150 | 2019-07-10 apples | USA-WA | 75 | 2019-07-01 apples | USA-CA | 75 | 2019-08-15 apples | Canada | 80 | 2019-08-01 (more…)


Elein's PlanetPostgreSQL, PostgreSQL
 LATERAL The primary feature of LATERAL JOIN is to enable access elements of a main query in a subquery which can be very powerful. Several common uses of LATERAL are to: denormalize arrays into parent child tables aggregation across several tables row or action generation. Note, however, that the subquery will execute for each main query row since the values used in the subquery will change. This might make a slower query. USAGE SELECT <target list> FROM <table> JOIN LATERAL (<subquery using table.column>) as foo; Here are three examples of using LATERAL.  Obviously there are many more.: Normalizing In the Normalization example we have a table (denorm) containing ids and an array of other ids. We want to to flatten the arrays, creating (more…)