1- To find blocks of contiguous values, you can use something similar to Gauss' trick for calculating arithmetic progressions: sort them by descending order and add each value to the row number. All contiguous values will add to the same number. You can then apply max/min and get rows that correspond to the blocks of values.
select min(n), max(n) from (
select n, n+row_number() over (order by n desc) group
from numbers
)
group by group
order by 1
select date, inflation, (exp(accumulated)-1)*100 from (
select date, inflation, sum(ln(1+(inflation/100))) over (order by date desc rows between current row and 11 following) as accumulated
from inflation
)
It's been some time since I've done serious work with SQL yet I remember that all paging solutions I've found (eg. top results on SO) are always platform-specific so having a platform-independent way of doing it would mean I could finally try to remember it.
select ord, table_name, first_name, last_name, total_rows
from (
select table_name,
row_number() over (order by table_name asc) ord,
count(1) over () total_rows,
from all_tables
where table_name like 'B%'
order by table_name desc
) where ord between 10 and 19
order by ord
I like to add total_rows so that I can show a "rows 10 - 19 of 81" header. You can also get the very first and last values by adding theses columns:
first_value(table_name) over (order by table_name asc rows between unbounded preceding and unbounded following) first_name,
last_value(table_name) over (order by table_name asc rows between unbounded preceding and unbounded following) last_name
ord=1 or rownum=1 or ord between 10 and 19
• https://use-the-index-luke.com/sql/partial-results/fetch-nex...
As in: SELECT * FROM (SELECT *, row_number() OVER (order by <some column - primary key column would be a good choice>) as rowidx FROM your_table) as numbered WHERE rowidx > ? AND rowid < ?
https://www.postgresql.org/docs/13/tutorial-window.html
While you are at it, check out common table expressions as well
SELECT
tm.*
FROM
(
SELECT
row_number() OVER (
PARTITION BY tm_1.thread_id
ORDER BY tm_1.delivered_at DESC
) AS "row",
tm_1.*
FROM
thread_message tm_1
) tm
WHERE
tm."row" = 1;
SELECT app_name, MAX_BY(version_number, updated_at) as version_number FROM table GROUP BY 1
If you want to know the highest salary in each department, that's easy:
select department, max(gross_salary)
from salary
group by department
select department, first_name, max(gross_salary)
from salary
group by department
Window functions let you re-frame this kind of group by like this:
select department, gross_salary
from (
select *, row_number() over (partition by department order by gross_salary desc) as n
from salary
) _
where n = 1
select department, first_name, gross_salary
from (
select *, row_number() over (partition by department order by gross_salary desc) as n
from salary
) _
where n = 1
You can do this with a LATERAL join, if you want to avoid the jankiness of window functions. Lateral joins are just a programmatic way to introduce a correlated subquery. For example
SELECT department, first_name, gross_salary FROM
(SELECT DISTINCT department FROM salary) depts,
LATERAL (
SELECT first_name, gross_salary
FROM salary
WHERE department = depts.department
ORDER BY gross_salary DESC
LIMIT 3
)
Well, there is a way which is window functions :) as shown by you.
The idea to expect exactly one first name of the person with the biggest salary is kinda wrong, since there can be more than one person, and this can obviously not described as a single column per group.
Note that aggregating is not limited to min, max, sum, etc. Postgres, for example, has array_agg which aggregates individual columns from each record of a group into an array, if that becomes necessary.
That's true. For this idea to work, there would need to be some framework for picking exactly one row from a group. The simplest thing would be to raise an error if there were multiple candidates, but perhaps there are better ways. I think the operation of picking exactly zero or one things from a group is so common that it's worth making provision for it.
select department, max(gross_salary), arg_max(gross_salary, first_name)
from salary
group by department
SELECT department, first_name, salary
FROM salary AS s
WHERE s.[salary] = (
SELECT MAX(ex.[salary])
FROM salary AS ex
WHERE s.[department] = ex.[department])The "lag" and "lead" SQL functions are particularly useful when analysing sequences of timestamped events (GPS fixes, task completions etc). They allow you to easily return the delta of a value (time/distance) between the current and previous row, which is really useful if you want to compare expected vs actual on those deltas.
Why is this the case? Shouldn't CTEs be more efficient?
https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
If you where speaking of MSSQL, it does treats non-recursive[1] CTE's as equivalent to to a view, and effectively inline it. So if things were faster with a temp table than a CTE it would mean that the query optimizer came up with a bad query plan. The most likely reason being that your query was complicated enough that you confused the cardinality estimator, so it ends up choosing really bad join types.
[1] Recursive queries in MSSQL are more complicated. They spool, but not necessarily all rows get spooled right away. So an infinite result recursive CTE does not necessarily fail. In any case predicate pushdown still occurs.
In the case of a temp table, you're likely intelligently (manually) going through the process of whittling down the data to only what you need and in the most efficient ways possible. Maybe you're filtering by date upfront, then by some other filter, removing as many rows as is possible as quickly as possible. With the CTE(s), it might be creating a list of many filters with a lot of joins in one step, and the optimizer may not understand which filters can remove the most rows the most easily.
Well, at least with PostgreSQL -- all CTEs were always materialized. It was optimized only in the version 12.
https://towardsdatascience.com/analytical-functions-in-oracl...
Favorite/weirdest thing I learned to do most recently with window functions is using/abusing min() and max(). If there is no value in a column in a particular window, min and max will give NULL.
We use this to do really interesting stuff (entirely in sql!) like "partition usage into sessions where a session is defined as a bunch of activity by a user where they did an action at least once every 30 minutes"
That kind of query happens all the time, and min/max behavior isn't the safest one.
Helpful resource: http://www.windowfunctions.com/
With mything as ( Select * from table where... ),
Myotherthing as ( Select * from mything where... )
Get translated to
Select * from (select * from ( select * from...)...)...)
So I'm just wondering are CTEs just easier to read, or do they offer any other known optimizations? We use them loads but mainly just for keeping larger queries easier to manage
https://www.postgresql.org/docs/13/queries-with.html
> A useful property of WITH queries is that they are normally evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects. However, the other side of this coin is that the optimizer is not able to push restrictions from the parent query down into a multiply-referenced WITH query, since that might affect all uses of the WITH query's output when it should affect only one. The multiply-referenced WITH query will be evaluated as written, without suppression of rows that the parent query might discard afterwards. (But, as mentioned above, evaluation might stop early if the reference(s) to the query demand only a limited number of rows.)
> However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references the WITH query just once, but not if it references the WITH query more than once. You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query, or by specifying NOT MATERIALIZED to force it to be merged into the parent query. The latter choice risks duplicate computation of the WITH query, but it can still give a net savings if each usage of the WITH query needs only a small part of the WITH query's full output.
There are some examples below this text as well.
So the planner would optimise each CTE separately, but wouldn’t optimise the entire query with all CTEs together, which of course can result in some slightly nonsensical query plans.
To my knowledge this is considered a limitation rather than a feature as it can cause performance issues with some queries.
EDIT: Some quick Googling suggests the CTEs are no longer optimisation fences, and their handling can be tweaked on a CTE by CTE basic in Postgres 12 onwards [1]
[1] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
" Allow common table expressions (CTEs) to be inlined into the outer query (Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)
Specifically, CTEs are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by specifying NOT MATERIALIZED. Previously, CTEs were never inlined and were always evaluated before the rest of the query. "
[0] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... [1] https://www.postgresql.org/docs/12/release-12.html
There are though things that CTEs can do and sub-selects can't (e.g. WITH RECURSIVE)
The problem is ofc scalability, if your team is small it is better to just have some few, but very specific queries, and do whatever transactions required on a layer above.
It may be the difference between correcting a few lines, vs reworking 20+ different queries, and testing each one individually.