correlate subquery in hive

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.

Replay

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

Category: sql Time: 2016-07-28 Views: 0
Tags: sql

Related post

  • Hive : Multiple In-elements with single subquery 2016-01-18

    I was trying to run a query like this on hive (version 1.2.1) : DELETE FROM employee as e WHERE (e.id, e.name) IN ( SELECT emp.id, emp.name FROM employee_final emp) AND e.sno = 120 ; But since hive doesn't support this construct so I tried this : DEL

  • Nested subquery not supported in hive 2016-01-21

    We have tried the below query in hive. but getting the error. please help me to resolve this in any other way. select count(1) as OpenItems from issues i , issue_statuses s where s.id = i.status_id and s.name NOT IN ('Closed','Passed','Rejected','On

  • Subquery returned more than 1 value 2013-06-12

    In my database I have two tables: projects and comp_types. When a new project is created a trigger is hit which inserts a concatenated value in a column in the projects table. A fully working SQL Fiddle. Please run this and you will see, it works. Ho

  • Change Data Capture using HIVE 2014-10-22

    Could you please review my code and let me know if there is something wrong? I'm getting the expected results. I'm trying to do change data capture using hive. We already have an existing CDC in DataStage, but we are trying to implement in HIVE now.

  • NoViableAltException([email protected][]) exception while parsing hive query 2016-02-15

    I had requirement in which I am parsing hive queries using following classes from hive-exex jar org.apache.hadoop.hive.ql.parse.ParseDriver,org.apache.hadoop.hive.ql.parse.ASTNode,org.apache.hadoop.hive.ql.parse.ParseUtils,org.apache.hadoop.hive.ql.p

  • How to edit registry hives without live running 2009-06-08

    I have a windows Vista system that had the underlying disk partitions changed. The number and logical location of the partitions didn't change as far as I can tell). When it boots up it says I need to restart immediately and then when I've restarted,

  • hadoop/hive metastore 2009-08-07

    where do people place their multi user meta store? I'm going to use mysql but I don't know were I should stick it. on the name node or on its own server? --------------Solutions------------- Hadoop can really trash the page buffers on a datanode so y

  • Is there way to scan a registry hive on an attached USB drive? 2010-02-08

    I have a hard drive with a virus that I removed from a PC. I can scan the file system of it as an attached USB drive. But how do I scan the registry of that USB drive since it is not booted up like a regular hard drive? EDIT: To clarify, the USB driv

  • How to compact a registry hive? 2010-04-14

    I'm using a (non-administrator) roaming profile, with a size limit of 4MB. As you can imagine, it is extremely difficult to stay within that size limit. I've noticed that NTUser.dat, which holds my HKEY_CURRENT_USER hive, is 2560KB, single-handedly u

  • 12 hive, what does the 12 represent? 2010-05-20

    Perhaps a silly question, but what does the 12 in "web server extensions/12" mean? --------------Solutions------------- It's the internal version number used by Microsoft. Although you think of it as Office 2007/SharePoint 2007, before the produ

  • Even data distribution on hadoop/hive 2010-07-06

    I am trying a small hadoop setup (for experimentation) with just 2 machines. I am loading about 13GB of data, a table of around 39 million rows, with a replication factor of 1 using Hive. My problem is hadoop always stores all this data on a single d

  • Why is there no "13" hive in SharePoint Server? 2010-07-14

    I know it's not hugely technical, but did anyone ever get an answer to why there is no "13" hive in SharePoint. Does the old superstition still reign supreme? Or was there a Beta version somewhere between 2007 and 2010. Did Microsoft ever make a

  • install SQL Server 2008 R2 on Chinese Vista fails: Checks if existing performance counter registry hive is consistent 2010-08-06

    PerfMonCounterNotCorruptedCheck Checks if existing performance counter registry hive is consistent. Failed The performance counter registry hive is corrupted. To continue, you must repair the performance counter registry hive. For more information, s

  • Can u explain how to export & import the hive HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0? 2011-01-24

    Error Message : "Share point server is not installed" --------------Solutions------------- You need to run the SharePoint product install properly. There is more to it than just putting files in the 14 folder (we are not supposed to say 'hive' a

  • MS SQL Query Sum of subquery 2011-01-31

    I need a help i getting following output from the query . SELECT ARG_CONSUMER, cast(ARG_TOTALAMT as float)/100 AS 'Total', (SELECT SUM(cast(DAMT as float))/100 FROM DEBT WHERE DDATE >= ARG.ARG_ORIGDATE AND DDATE <= ARG.ARG_LASTPAYDATE AND DTYPE IN (

  • Problem with MySQL subquery 2011-02-21

    Why does this query DELETE FROM test WHERE id=(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); sometimes delete 1 row, sometimes 2 rows and sometimes nothing? If I write it in this form: SET @var = (SELECT id FROM (SELECT * FROM te

  • How to convert a Top 1 subquery using outer table alias to Oracle? 2011-03-31

    I have the following SQL Server query select (select top 1 b2 from BB b where b.b1 = a.a1 order by b2) calc, a1, a2 from AA a where a2 = 2; which I can rewrite using analytic functions select (select b2 from (select row_number() over (order by b2) lf

  • how to test whether a Windows 2000 registry hive is corrupt 2011-04-15

    I have an old system that has Windows 2000 on it (yes, I should retire it), that recently stopped booting, with the message "Windows Could Not Start Because the Following File Is Missing or Corrupt: \Winnt\System32\Config\Systemced" Searching ar

  • Where is the masterpage in 14 hive in sp 2010 2011-04-28

    I need to make a copy of the master page in sp 2010. where is it located? (I dont have SP Designer so that's not an option). --------------Solutions------------- It's in the 14 hive at: C:\Program Files\Common Files\Microsoft Shared\Web Server Extens

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.825 (s). 13 q(s)