[
https://issues.apache.org/jira/browse/DRILL-4092?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16797211#comment-16797211
]
benj commented on DRILL-4092:
-
Until the feature is implemented, there are queries to achieve the same result.
with file1.csvh and file2.csvh each containing only column a
without INTERSECT we can do
{code:java}
/* Simulate INTERSECT */
SELECT DISTINCT a FROM
(
SELECT a
, min(idfile) over(PARTITION BY a) AS minc
, max(idfile) over(PARTITION BY a) AS maxc
FROM
(
SELECT a, 0 idfile FROM `file1.csvh`
UNION ALL
SELECT a, 1 idfile FROM `file2.csvh`
) AS lvl_union
) AS lvl_over WHERE minc = 0 and maxc = 1{code}
But it's a little bit more complicated for INTERSECT ALL (as defined in 7.12 of
ansi-iso-9075-2-1999.pdf)
{code:java}
/* Simulate INTERSECT ALL */
SELECT a FROM
(
SELECT a, c
, min(idfile) over(PARTITION BY a) AS minc
, max(idfile) over(PARTITION BY a) AS maxc
, count(a) OVER(PARTITION BY a, c) AS nb_elt_by_table
,LEAST(count(CASE WHEN c = 0 THEN 1 END) OVER (PARTITION BY a)
, count(CASE WHEN c = 1 THEN 1 END) OVER (PARTITION BY a)) min_elt
FROM
(
SELECT a, 0 idfile FROM `file1.csvh`
UNION ALL
SELECT a, 1 idfile FROM `file2.csvh`
) AS lvl_union
) AS lvl_over
WHERE minc = 0 and maxc = 1 AND nb_elt_by_table = min_elt
{code}
Hoping that this can help until we can use the standard SQL syntax.
> Support for INTERSECT
> --
>
> Key: DRILL-4092
> URL: https://issues.apache.org/jira/browse/DRILL-4092
> Project: Apache Drill
> Issue Type: New Feature
>Reporter: Victoria Markman
>Assignee: Prasad Nagaraj Subramanya
>Priority: Major
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
