You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by br...@apache.org on 2018/08/03 21:18:39 UTC
[drill-site] branch asf-site updated: edits to lateral join
This is an automated email from the ASF dual-hosted git repository.
bridgetb pushed a commit to branch asf-site
in repository https://gitbox.apache.org/repos/asf/drill-site.git
The following commit(s) were added to refs/heads/asf-site by this push:
new caf5418 edits to lateral join
caf5418 is described below
commit caf541894a0d80fb714859961611f01af1c9e8b5
Author: Bridget Bevens <bb...@maprtech.com>
AuthorDate: Fri Aug 3 14:18:23 2018 -0700
edits to lateral join
---
docs/lateral-join/index.html | 132 ++++++++++++++++++++++++++++++++++++++++---
feed.xml | 4 +-
2 files changed, 127 insertions(+), 9 deletions(-)
diff --git a/docs/lateral-join/index.html b/docs/lateral-join/index.html
index c41197a..c47ec65 100644
--- a/docs/lateral-join/index.html
+++ b/docs/lateral-join/index.html
@@ -1250,7 +1250,7 @@
</div>
- Aug 2, 2018
+ Aug 3, 2018
<link href="/css/docpage.css" rel="stylesheet" type="text/css">
@@ -1296,9 +1296,7 @@ tableReference:
<ul>
<li><p><em>join_clause</em><br>
- Identifies the tables with the data you want to join, the type of join to be performed on the tables, and the conditions on which to join the tables. Starting in Drill 1.14, Drill supports lateral joins. </p>
-
-<p><strong>NOTE:</strong> See LATERAL Join for additional information and examples of queries with lateral joins. </p></li>
+ Identifies the tables with the data you want to join, the type of join to be performed on the tables, and the conditions on which to join the tables. Starting in Drill 1.14, Drill supports lateral joins. </p></li>
<li><p><em>LATERAL</em><br>
Keyword that represents a lateral join. A lateral join is essentially a foreach loop in SQL. A lateral join combines the results of the outer query with the results of a lateral subquery. When you use the UNNEST relational operator, Drill infers the LATERAL keyword. </p></li>
<li><p><em>lateral_sub_query</em><br>
@@ -1368,9 +1366,129 @@ tableReference:
<p>The following customer table contains customer data, including customer orders and returns, with the order and return data stored as complex types (arrays of maps). The store_id column is a foreign key. </p>
-<p>INSERT TABLE</p>
-
-<p>If you want to query the Customer table for the average order amount for each customer in the month of November, you could write the following query, which uses a combination of left outer join, group by, and flatten operations: </p>
+<table class=MsoNormalTable border=0 cellspacing=0 cellpadding=0
+ style='border-collapse:collapse'>
+ <tr style='height:.5in'>
+ <td valign=top style='border:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;
+ height:.5in'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>cust_id</span></p>
+ </td>
+ <td valign=top style='border:solid black 1.0pt;border-left:none;padding:5.0pt 5.0pt 5.0pt 5.0pt;
+ height:.5in'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>cust_name</span></p>
+ </td>
+ <td valign=top style='border:solid black 1.0pt;border-left:none;padding:5.0pt 5.0pt 5.0pt 5.0pt;
+ height:.5in'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>store_id<br>
+ </span></p>
+ </td>
+ <td valign=top style='border:solid black 1.0pt;border-left:none;padding:5.0pt 5.0pt 5.0pt 5.0pt;
+ height:.5in'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>orders</span></p>
+ </td>
+ <td valign=top style='border:solid black 1.0pt;border-left:none;padding:5.0pt 5.0pt 5.0pt 5.0pt;
+ height:.5in'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>returns</span></p>
+ </td>
+ </tr>
+ <tr style='height:135.0pt'>
+ <td valign=top style='border:solid black 1.0pt;border-top:none;padding:5.0pt 5.0pt 5.0pt 5.0pt;
+ height:135.0pt'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>101</span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:135.0pt'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>Fred</span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:135.0pt'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>5</span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:135.0pt'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>[<br>
+ {order_id: 1, order_date: 10/10/2017, order_amount: $200, items:<br>
+ [{type: “chair”, quantity: 3}, {type: ...} ] },</span></p>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'> {order_id: 2,
+ order_date:<br>
+ 11/10/2017, order_amount: $500, items: [{type: “lamp”, quantity:
+ 2},<br>
+ {type: ...}]}</span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:135.0pt'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'> [ {order_id: 2,
+ return_date: 11/20/2017, return_amount: $200,<br>
+ items: [{type: ...} ] } ]</span></p>
+ </td>
+ </tr>
+ <tr style='height:.5in'>
+ <td valign=top style='border:solid black 1.0pt;border-top:none;padding:5.0pt 5.0pt 5.0pt 5.0pt;
+ height:.5in'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>102</span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:.5in'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>Jack</span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:.5in'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>7</span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:.5in'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'><orders<br>
+ data></span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:.5in'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'><returns<br>
+ data></span></p>
+ </td>
+ </tr>
+ <tr style='height:23.0pt'>
+ <td valign=top style='border:solid black 1.0pt;border-top:none;padding:5.0pt 5.0pt 5.0pt 5.0pt;
+ height:23.0pt'></td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:23.0pt'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>...</span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:23.0pt'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>...</span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:23.0pt'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>...</span></p>
+ </td>
+ <td valign=top style='border-top:none;border-left:none;border-bottom:solid black 1.0pt;
+ border-right:solid black 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:23.0pt'>
+ <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
+ normal'><span style='font-size:9.0pt;color:black'>...</span></p>
+ </td>
+ </tr>
+</table>
+
+<p>If you want to query the Customer table for the average order amount for each customer in the month of November, you could write the following query, which uses a combination of a left outer join, group by, and the flatten function to unnest the orders field that contains complex types: </p>
<div class="highlight"><pre><code class="language-text" data-lang="text"> SELECT t1.cust_name, t2.avg_orders
FROM customer t1
LEFT
diff --git a/feed.xml b/feed.xml
index 425e34d..8a23188 100644
--- a/feed.xml
+++ b/feed.xml
@@ -6,8 +6,8 @@
</description>
<link>/</link>
<atom:link href="/feed.xml" rel="self" type="application/rss+xml"/>
- <pubDate>Thu, 02 Aug 2018 19:11:16 -0700</pubDate>
- <lastBuildDate>Thu, 02 Aug 2018 19:11:16 -0700</lastBuildDate>
+ <pubDate>Fri, 03 Aug 2018 14:16:31 -0700</pubDate>
+ <lastBuildDate>Fri, 03 Aug 2018 14:16:31 -0700</lastBuildDate>
<generator>Jekyll v2.5.2</generator>
<item>