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 <[email protected]>
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>
