Skip to content
Advertisement

Optimizing SQL query – finding a group with in a group

I have a working query and looking for ideas to optimize it.

Query explanation: Within each ID group (visitor_id), look for row where c_id != 0. From that row, show all consecutive rows within that ID group.

Advertisement

Answer

so you have a common sub expression

so that can be moved to a CTE and run just once. like

but the sub clause filter is equally valid as a top level filter, and given it’s a value inclusive range filter BETWEEN will give better performance.

then both uses of that CTE do the same ROW_NUMBER operation so that can be a CTE

and simplified as such

So we are want to keep all rows that come after the first non-zero c_id which a QUALIFY should be able to solve like:

which without have run feels like the MIN also should be able to be moved to the QUALIFY like:

At which point the CTE is not needed, as it’s just used once, so could be moved back in, or not as they are the same.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement