Correlate subquery in hive

hivesubquery

SELECT ndo.sku ParentSKU, visitsWhenSKUWasOnline.s
FROM 
Temp.NumberOfDaysOnline ndo 
JOIN
(
    SELECT SUM(gasessiondata.sessions) as s
    FROM gasessiondata
    WHERE
    gasessiondata.date >= ndo.FromDate
    AND
    gasessiondata.date <= ndo.ToDate
  ) as visitsWhenSKUWasOnline
  ON 1=1

I can't use ndo.fromDate and ndo.ToDate inside of the subquery since hive does not support correlated sub queries. What could be an alternative for something like this?

Edit:

down vote
favorite
I have a table like this:

date          val

2016-1-1       8
2016-2-1       10
2016-1-2       30
2016-1-3       30

Now, I have two dates coming in from a different table, let's call them `fromdate` and todate

If fromdate was 1, 2016-1-1 and Todate was 2016-1-2, I need 8+30 = 38 as the final value

I need to check

SUM(val) of all those values dates corresponding to which are between the fromdate and twodate.

Best Answer

The syntax of you query is not often called "correlated subquery". A correlated subquery would be like this:

SELECT 
    ndo.sku AS ParentSKU, 
    (
        SELECT SUM(g.sessions)
        FROM gasessiondata AS g
        WHERE g.date >= ndo.FromDate
          AND g.date <= ndo.ToDate
    ) AS s
FROM 
    Temp.NumberOfDaysOnline AS ndo ;

Your query is similar to a LATERAL join (CROSS / OUTER APPLY in SQL Server). This type of correlated joins has not been implemented in many DBMS.

It's easy to rewrite though so the join is done first and then the GROUP BY. This simple query should be supported by most, if not all DBMS:

SELECT 
    ndo.sku AS ParentSKU, 
    SUM(g.sessions) AS s
FROM 
        Temp.NumberOfDaysOnline AS ndo 
    JOIN
        gasessiondata AS g
    ON  g.date >= ndo.FromDate
    AND g.date <= ndo.ToDate
GROUP BY 
    ndo.PK,             -- the PRIMARY KEY of the table
    ndo.sku ;           -- and any column used in the SELECT list

Seems that Hive supports only equi-joins and cross joins. Moving the condition to the WHERE clause might solve this:

SELECT 
    ndo.sku AS ParentSKU, 
    SUM(g.sessions) AS s
FROM 
        Temp.NumberOfDaysOnline AS ndo 
    CROSS JOIN
        gasessiondata AS g
WHERE 
        g.date >= ndo.FromDate
    AND g.date <= ndo.ToDate
GROUP BY 
    ndo.PK,             -- the PRIMARY KEY of the table
    ndo.sku ;           -- and any column used in the SELECT list

Related Solutions

MySQL – How to Iterate Subquery in Aggregate Select

(sum(amount * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50)))) as cost Is the answer, as provided by ypercube.

I was victim to order of operations--MySQL was taking the sum of gallons pumped, then multiplying that against the first/last result found from the gas prices sub query. As Michael quoted and noted:

"The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

So to get what I was after, the sum of each entry multiplied against the price of gas during that week, you simply wrap that exact expression in the comfortable embrace of a sum().

SQL Server Optimization – Why SQL Server Runs Subquery for Each Row

The slow plan isn't calculating the MAX for each row in the outer query.

In fact it never explicitly calculates it at all.

It gives a plan similar to

WITH CTE
     AS (SELECT TOP(1) WITH TIES *
         FROM   SubqueryTest
         WHERE year IS NOT NULL
         ORDER  BY year desc)
SELECT month,
       count(*)
FROM   CTE
GROUP  BY month

Slow Plan (Estimated Row Counts)

enter image description here

You have a non covering index on year asc so it scans that backwards to get the rows in the first year (shows as a seek because of the implicit IS NOT NULL predicate).

Unfortunately it doesn't seem to differentiate between TOP 1 and TOP 1 WITH TIES when estimating row counts.

In this case it makes a huge difference. (estimated 2 key lookup vs actual 4,424,803) so you get an inappropriate plan.

Slow Plan (Actual Row Counts)

enter image description here

You could consider adding month into the index on year either as a key or included column to make the index covering. The benefit of adding it as a secondary key column would be that it could then feed into a stream aggregate without an additional sort (though for only 12 distinct values a hash aggregate would be fine anyway).

A non covering index on such a non selective column is really pretty useless for the vast majority of queries. The index is totally ignored by the "fast" plan which ends up doing a parallel scan on the whole table and evaluating the predicate on all 27,445,400 rows (in preference to performing the huge number of lookups).

enter image description here

Best Answer

Related Solutions

MySQL – How to Iterate Subquery in Aggregate Select

SQL Server Optimization – Why SQL Server Runs Subquery for Each Row

Related Question