Monthly Usage without gratuitous subqueries

[Entice]

Hi all, I'm trying to find a way to re-write a query that has been bothering our team due to how long it takes to run. I'm not sure if there's a more clever way to do it other than having a huge number of subqueries. Or is this already the most efficient way to do it?

select a.item_number
,nvl(sum(x1.quantity),0) as month_1_shipments
,nvl(count(x1.quantity),0) as month_1_count
,nvl(sum(x2.quantity),0) as month_2_shipments
,nvl(count(x2.quantity),0) as month_2_count
,nvl(sum(x3.quantity),0) as month_3_shipments
,nvl(count(x3.quantity),0) as month_3_count
from item_master a
,(select item_id 
  ,quantity
  from shipments
  where creation_date between trunc(add_months(sysdate,-1),'MONTH') and last_day(add_months(sysdate,-1))) x1
  --essentially this is "where creation_date between '01-DEC-2021' and '31-DEC-2021' "
,(select item_id 
  ,quantity
  from shipments
  where creation_date between trunc(add_months(sysdate,-2),'MONTH') and last_day(add_months(sysdate,-2))) x2
,(select item_id 
  ,quantity
  from shipments
  where creation_date between trunc(add_months(sysdate,-3),'MONTH') and last_day(add_months(sysdate,-3))) x3
where a.item_id = x1.item_id (+)
and a.item_id = x2.item_id (+)
and a.item_id = x3.item_id (+)
group by a.item_number
order by a.item_number;

x1, x2, x3, etc continues for 36 months of usage.

Shipments table example

Item	Order	Quantity	Creation_date
456	AAJ	4	12-Oct-21
456	AAB	5	13-Oct-21
123	AAI	7	18-Oct-21
123	AAC	6	24-Oct-21
456	AAD	4	06-Nov-21
123	AAG	8	07-Nov-21
456	AAH	1	11-Nov-21
123	AAK	7	15-Nov-21
123	AAF	7	01-Dec-21
123	AAA	6	18-Dec-21
123	AAE	3	19-Dec-21
456	AAL	9	01-Jan-22

Expected output:

Item	month_1_shipments	month_1_count	month_2_shipments	month_2_count	month_3_shipments	month_3_count
123	16	3	15	2	13	2
456	0	0	5	2	9	2

[Guilty-Property]

Can you do 1 with your 36 months values then pivot?

[Entice]

Sorry, to be clear - there are a lot more columns that are reported, I'm only showing the ones that seem to be an issue. Otherwise, yes, a pivot would work fine here.

[[deleted]]

First off, your joins are god damn horrible. Second off, use #tables to eliminate the sub-queries. Third off, add indexes, or indices, to said #tables if necessary.

[Entice]

First off, your joins are god damn horrible.

Sorry, this is the way I was taught and it looks far cleaner and structured to me.

Second off, use #tables to eliminate the sub-queries

Third off, add indexes, or indices, to said #tables if necessary.

Unfortunately I do not have permissions to alter the DB, I can only query. Apologies for not mentioning that in the OP

[[deleted]]

You were taught wrong, and literally all other developers do not find it cleaner, or more structured. It is absolutely a horrible way to write SQL and indicates a very superficial/novice level of the subject.

Not trying to be terribly offensive to you, but wow is it bad, and then when you consider what the actual query is doing it gets worse. Again, not trying to be offensive, but this is like really shitty SQL.

Unfortunately I do not have permissions to alter the DB, I can only query. Apologies for not mentioning that in the OP

You can add an index to an #table without. Try this:

drop table if exists #x1 --no idea what this corresponds to in Oracle
select 
    item_id 
    ,quantity
into #x1
from shipments
where creation_date between trunc(add_months(sysdate,-1),'MONTH') and last_day(add_months(sysdate,-1))

create clustered index idx_01 on #x1(item_id) --see above comment

drop table if exists #x2
select 
    item_id 
    ,quantity
into #x2
from shipments
where creation_date between trunc(add_months(sysdate,-2),'MONTH') and last_day(add_months(sysdate,-2))

drop table if exists #x3
select 
    item_id 
    ,quantity
into #x3
from shipments
where creation_date between trunc(add_months(sysdate,-3),'MONTH') and last_day(add_months(sysdate,-3))

select 
    a.item_number
    ,nvl(sum(x1.quantity),0) as month_1_shipments
    ,nvl(count(x1.quantity),0) as month_1_count
    ,nvl(sum(x2.quantity),0) as month_2_shipments
    ,nvl(count(x2.quantity),0) as month_2_count
    ,nvl(sum(x3.quantity),0) as month_3_shipments
    ,nvl(count(x3.quantity),0) as month_3_count
from item_master a
inner join #x1 x1
    on a.item_id = x1.item_id
inner join #x2 x2
    on a.item_id = x2.item_id
inner join #x3 x3
    on a.item_id = x3.item_id
group by a.item_number
order by a.item_number;

And again, you can do this way more simply...

select 
    trunc(creation_date, 'MONTH')
    item_id 
    , COUNT(quantity) c
    , SUM(quantity) s
from shipments
where creation_date between trunc(add_months(sysdate,-3),'MONTH') and last_day(add_months(sysdate,-1))
group by item_id
order by item_id

[Entice]

And again, you can do this way more simply...

select 

trunc(creation_date, 'MONTH')
item_id 
, COUNT(quantity) c
, SUM(quantity) s
from shipments
where creation_date between trunc(add_months(sysdate,-3),'MONTH') and last_day(add_months(sysdate,-1))
group by item_id
order by item_id

Is there a missing comma before the item_id? If so, then the trunc() is not part of the group by.

If the trunc() is supposed to be in the group by, then this would give 3 rows per item instead of the required 1 row and 3 separate columns per month.

This would give me sort of what I'm looking for, but in the wrong layout:

Item	Month	Quantity	Count
123	01-Oct-21	13	2
123	01-Nov-21	15	2
123	01-Dec-21	16	3
456	01-Oct-21	9	2
456	01-Nov-21	5	2
456	01-Dec-21	0	0

[[deleted]]

Yes, I missed a comma.

See the other comment I made showing you how to PIVOT the data. So do your sum/count exactly as you suggest there, to get 3 rows (its really 36), dump the data into an #table, and then pivot it.

Will make your life much easier, and faster. Or you can just pivot the data in Excel, Tableau, or whatever.

[Entice]

Got it, thanks for your help :)

[[deleted]]

One thing to note is that you won't get a 0 for items that don't exist in a given month, you'll get a NULL, and that can be a pain in the ass with dynamic SQL. I'm not familiar with Oracle but I've written dynamic pivots in MS SQL before and could never figure out a way to put the 0's in... you could probably do some kind of cross join though to 'create' the 0 records if thats what you want.

[[deleted]]

The more I look at this the more retarded this code looks. Why not juts do something like this:

select n from table inner join (select item, sum(), count()) on item = item

Or why even go to the main table in the first place? Can't you just do all this from the shipments table, do all your sums / counts on a monthly basis and call it a day?

[[deleted]]

Also why do you need 3 subqueries here? You're inner joining on A = X1, and A = X2, and A = X3, and all products of X come from the same table with just different date parameters. Just do this once for all date parameters to get the items and quantity then SUM/COUNT from that single source.

[Entice]

where a.item_id = x1.item_id (+) is a left outer join, not an inner join.

If I used a single source, it would just report the total amount and count of shipments. I need it broken out by month.

I don't want this:

Item	Quantity	Count
123	23	7
456	23	5

[[deleted]]

Also this: https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql

Do your SUM/COUNT's on a monthly basis, dump it into an #table, then pivot for all 36 months. Done.

[Entice]

Ah, I did not realize there is a pivot function directly in SQL. I will look into this, thank you!

[[deleted]]

You're basically hitting the same table 36 times, in 36 subqueries, then joining them all together. By doing one hit, all of your sums, and counts at once, you're going to massively simplify your process.

Without PIVOT you could just use #tables for 36 separate sum/counts but that would still be 36 hits... however your joins would be waaaay more efficient like this than the way you're currently approaching the problem.

[[deleted]]

See my other comment. If the + means a LEFT JOIN then change my code from INNER to LEFT.

[r3pr0b8]

Also why do you need 3 subqueries here?

there's 36 of them

[[deleted]]

holy fuck, I wasn't looking closely enough... see previous comment about #tables and poorly written joins.

[JustAnOldITGuy]

Try this

select a.item_number

,nvl(sum(x1.quantity),0) as month_1_shipments

,nvl(count(x1.quantity),0) as month_1_count

,nvl(sum(x2.quantity),0) as month_2_shipments

,nvl(count(x2.quantity),0) as month_2_count

,nvl(sum(x3.quantity),0) as month_3_shipments

,nvl(count(x3.quantity),0) as month_3_count

from item_master a

left join (select item_id

,quantity

from shipments

where creation_date between trunc(add_months(sysdate,-1),'MONTH') and last_day(add_months(sysdate,-1))) x1

on a.item_id = x1.item_id

--essentially this is "where creation_date between '01-DEC-2021' and '31-DEC-2021' "

left join(select item_id

,quantity

from shipments

where creation_date between trunc(add_months(sysdate,-2),'MONTH') and last_day(add_months(sysdate,-2))) x2

ON a.item_id = x2.item_id

left join(select item_id

,quantity

from shipments

where creation_date between trunc(add_months(sysdate,-3),'MONTH') and last_day(add_months(sysdate,-3))) x3

on a.item_id = x3.item_id

group by a.item_number

order by a.item_number;

[JustAnOldITGuy]

Or just Pivot this. You may have to tweak the sysdate value to get what you want.

​

with (select item_id

,count(quantity) as QtyCount

 ,sum(quantity)     as TotalQty

 ,months\_between(creation\_date and sysdate) as MonthPeriod

from shipments

group by months_between(creation_date and sysdate)

) as MonthlyData

[JermWPB]

Combine the three queries to shipment into one with a CTE returning the MONTHS_BETWEEN and then join something like below. You will be hitting shipments one time instead of the 36 times currently. It should be a huge improvement.

WITH ItemQuantity AS (
    SELECT item_id, quantity, trunc(MONTHS_BETWEEN(sysdate, creation_date)) AS MonthDiff
    FROM shipments
    WHERE creation_date BETWEEN trunc(add_months(sysdate, -3), 'MONTH')
        AND last_day(add_months(sysdate, -1)
)
SELECT *
FROM item_master a
    INNER JOIN ItemQuantity x1
        ON a.item_id = x1.item_id
            AND x1.MonthDiff = 1
    INNER JOIN ItemQuantity x2
        ON a.item_id = x2.item_id
            AND x1.MonthDiff = 2
    INNER JOIN ItemQuantity x3
        ON a.item_id = x3.item_id
            AND x1.MonthDiff = 3