Skip to content

Instantly share code, notes, and snippets.

@gumdropsteve

gumdropsteve/nested_queries.ipynb

Last active November 25, 2020 13:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save gumdropsteve/51d39e477422d8dd521fc2131984755b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save gumdropsteve/51d39e477422d8dd521fc2131984755b to your computer and use it in GitHub Desktop.
Download ZIP
Run these notebooks on app.blazingsql.com
Display the source blob
Display the rendered blob
Raw
nested_queries.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Nested SQL Queries with BlazingSQL"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"BlazingContext ready\n"
]
}
],
"source": [
"from blazingsql import BlazingContext\n",
"\n",
"bc = BlazingContext()\n",
"\n",
"bc.s3('blazingsql-colab', bucket_name='blazingsql-colab')\n",
"\n",
"bc.create_table('taxi', 's3://blazingsql-colab/yellow_taxi/taxi_data.parquet')"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>VendorID</th>\n",
" <th>tpep_pickup_datetime</th>\n",
" <th>tpep_dropoff_datetime</th>\n",
" <th>passenger_count</th>\n",
" <th>trip_distance</th>\n",
" <th>pickup_x</th>\n",
" <th>pickup_y</th>\n",
" <th>RateCodeID</th>\n",
" <th>store_and_fwd_flag</th>\n",
" <th>dropoff_x</th>\n",
" <th>dropoff_y</th>\n",
" <th>payment_type</th>\n",
" <th>fare_amount</th>\n",
" <th>extra</th>\n",
" <th>mta_tax</th>\n",
" <th>tip_amount</th>\n",
" <th>tolls_amount</th>\n",
" <th>total_amount</th>\n",
" <th>pickup_time</th>\n",
" <th>total_cost</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2.0</td>\n",
" <td>2015-03-07 02:17:05</td>\n",
" <td>2015-03-07 02:22:18</td>\n",
" <td>3.0</td>\n",
" <td>0.77</td>\n",
" <td>-8232200.852</td>\n",
" <td>4980818.134</td>\n",
" <td>1.0</td>\n",
" <td>N</td>\n",
" <td>-8231245.389</td>\n",
" <td>4980848.420</td>\n",
" <td>2.0</td>\n",
" <td>5.5</td>\n",
" <td>0.5</td>\n",
" <td>0.5</td>\n",
" <td>0.00</td>\n",
" <td>0.0</td>\n",
" <td>6.80</td>\n",
" <td>2015-03-07 02:17:05</td>\n",
" <td>6.80</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2.0</td>\n",
" <td>2015-02-05 21:05:35</td>\n",
" <td>2015-02-05 21:10:33</td>\n",
" <td>2.0</td>\n",
" <td>0.74</td>\n",
" <td>-8236124.619</td>\n",
" <td>4973741.097</td>\n",
" <td>1.0</td>\n",
" <td>N</td>\n",
" <td>-8234966.173</td>\n",
" <td>4972970.518</td>\n",
" <td>1.0</td>\n",
" <td>5.5</td>\n",
" <td>0.5</td>\n",
" <td>0.5</td>\n",
" <td>1.00</td>\n",
" <td>0.0</td>\n",
" <td>7.80</td>\n",
" <td>2015-02-05 21:05:35</td>\n",
" <td>6.80</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2.0</td>\n",
" <td>2015-02-04 23:25:27</td>\n",
" <td>2015-02-04 23:32:07</td>\n",
" <td>5.0</td>\n",
" <td>1.61</td>\n",
" <td>-8238354.032</td>\n",
" <td>4975721.343</td>\n",
" <td>1.0</td>\n",
" <td>N</td>\n",
" <td>-8236185.769</td>\n",
" <td>4976682.745</td>\n",
" <td>2.0</td>\n",
" <td>7.5</td>\n",
" <td>0.5</td>\n",
" <td>0.5</td>\n",
" <td>0.00</td>\n",
" <td>0.0</td>\n",
" <td>8.80</td>\n",
" <td>2015-02-04 23:25:27</td>\n",
" <td>8.80</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2.0</td>\n",
" <td>2015-01-25 14:27:24</td>\n",
" <td>2015-01-25 14:34:01</td>\n",
" <td>5.0</td>\n",
" <td>0.56</td>\n",
" <td>-8233618.334</td>\n",
" <td>4977926.822</td>\n",
" <td>1.0</td>\n",
" <td>N</td>\n",
" <td>-8234368.266</td>\n",
" <td>4977621.255</td>\n",
" <td>1.0</td>\n",
" <td>6.0</td>\n",
" <td>0.0</td>\n",
" <td>0.5</td>\n",
" <td>1.20</td>\n",
" <td>0.0</td>\n",
" <td>0.30</td>\n",
" <td>2015-01-25 14:27:24</td>\n",
" <td>-0.90</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2.0</td>\n",
" <td>2015-02-06 23:45:47</td>\n",
" <td>2015-02-07 00:21:40</td>\n",
" <td>2.0</td>\n",
" <td>3.85</td>\n",
" <td>-8237552.293</td>\n",
" <td>4971697.934</td>\n",
" <td>1.0</td>\n",
" <td>N</td>\n",
" <td>-8236852.469</td>\n",
" <td>4976189.980</td>\n",
" <td>2.0</td>\n",
" <td>23.0</td>\n",
" <td>0.5</td>\n",
" <td>0.5</td>\n",
" <td>0.00</td>\n",
" <td>0.0</td>\n",
" <td>24.30</td>\n",
" <td>2015-02-06 23:45:47</td>\n",
" <td>24.30</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>291542</th>\n",
" <td>2.0</td>\n",
" <td>2015-01-07 08:13:59</td>\n",
" <td>2015-01-07 08:37:49</td>\n",
" <td>3.0</td>\n",
" <td>2.28</td>\n",
" <td>-8232027.594</td>\n",
" <td>4978811.060</td>\n",
" <td>1.0</td>\n",
" <td>N</td>\n",
" <td>-8234805.656</td>\n",
" <td>4976368.247</td>\n",
" <td>1.0</td>\n",
" <td>15.5</td>\n",
" <td>0.0</td>\n",
" <td>0.5</td>\n",
" <td>2.32</td>\n",
" <td>0.0</td>\n",
" <td>0.30</td>\n",
" <td>2015-01-07 08:13:59</td>\n",
" <td>-2.02</td>\n",
" </tr>\n",
" <tr>\n",
" <th>291543</th>\n",
" <td>2.0</td>\n",
" <td>2015-01-03 17:42:31</td>\n",
" <td>2015-01-03 17:53:38</td>\n",
" <td>2.0</td>\n",
" <td>1.36</td>\n",
" <td>-8236281.740</td>\n",
" <td>4975538.043</td>\n",
" <td>1.0</td>\n",
" <td>N</td>\n",
" <td>-8235851.994</td>\n",
" <td>4977687.974</td>\n",
" <td>2.0</td>\n",
" <td>8.5</td>\n",
" <td>0.0</td>\n",
" <td>0.5</td>\n",
" <td>0.00</td>\n",
" <td>0.0</td>\n",
" <td>0.30</td>\n",
" <td>2015-01-03 17:42:31</td>\n",
" <td>0.30</td>\n",
" </tr>\n",
" <tr>\n",
" <th>291544</th>\n",
" <td>2.0</td>\n",
" <td>2015-02-28 08:29:29</td>\n",
" <td>2015-02-28 08:37:08</td>\n",
" <td>5.0</td>\n",
" <td>1.07</td>\n",
" <td>-8231666.642</td>\n",
" <td>4980123.266</td>\n",
" <td>1.0</td>\n",
" <td>N</td>\n",
" <td>-8232250.960</td>\n",
" <td>4981558.483</td>\n",
" <td>2.0</td>\n",
" <td>6.5</td>\n",
" <td>0.0</td>\n",
" <td>0.5</td>\n",
" <td>0.00</td>\n",
" <td>0.0</td>\n",
" <td>7.30</td>\n",
" <td>2015-02-28 08:29:29</td>\n",
" <td>7.30</td>\n",
" </tr>\n",
" <tr>\n",
" <th>291545</th>\n",
" <td>2.0</td>\n",
" <td>2015-03-13 16:57:37</td>\n",
" <td>2015-03-13 16:59:39</td>\n",
" <td>3.0</td>\n",
" <td>0.65</td>\n",
" <td>-8234554.263</td>\n",
" <td>4980903.944</td>\n",
" <td>1.0</td>\n",
" <td>N</td>\n",
" <td>-8235198.032</td>\n",
" <td>4979689.770</td>\n",
" <td>1.0</td>\n",
" <td>4.0</td>\n",
" <td>1.0</td>\n",
" <td>0.5</td>\n",
" <td>1.50</td>\n",
" <td>0.0</td>\n",
" <td>7.30</td>\n",
" <td>2015-03-13 16:57:37</td>\n",
" <td>5.80</td>\n",
" </tr>\n",
" <tr>\n",
" <th>291546</th>\n",
" <td>2.0</td>\n",
" <td>2015-02-05 16:19:05</td>\n",
" <td>2015-02-05 16:38:43</td>\n",
" <td>2.0</td>\n",
" <td>5.20</td>\n",
" <td>-8234358.074</td>\n",
" <td>4982376.298</td>\n",
" <td>1.0</td>\n",
" <td>N</td>\n",
" <td>-8236800.662</td>\n",
" <td>4974834.022</td>\n",
" <td>1.0</td>\n",
" <td>19.5</td>\n",
" <td>1.0</td>\n",
" <td>0.5</td>\n",
" <td>5.32</td>\n",
" <td>0.0</td>\n",
" <td>26.62</td>\n",
" <td>2015-02-05 16:19:05</td>\n",
" <td>21.30</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>291547 rows × 20 columns</p>\n",
"</div>"
],
"text/plain": [
" VendorID tpep_pickup_datetime tpep_dropoff_datetime passenger_count \\\n",
"0 2.0 2015-03-07 02:17:05 2015-03-07 02:22:18 3.0 \n",
"1 2.0 2015-02-05 21:05:35 2015-02-05 21:10:33 2.0 \n",
"2 2.0 2015-02-04 23:25:27 2015-02-04 23:32:07 5.0 \n",
"3 2.0 2015-01-25 14:27:24 2015-01-25 14:34:01 5.0 \n",
"4 2.0 2015-02-06 23:45:47 2015-02-07 00:21:40 2.0 \n",
"... ... ... ... ... \n",
"291542 2.0 2015-01-07 08:13:59 2015-01-07 08:37:49 3.0 \n",
"291543 2.0 2015-01-03 17:42:31 2015-01-03 17:53:38 2.0 \n",
"291544 2.0 2015-02-28 08:29:29 2015-02-28 08:37:08 5.0 \n",
"291545 2.0 2015-03-13 16:57:37 2015-03-13 16:59:39 3.0 \n",
"291546 2.0 2015-02-05 16:19:05 2015-02-05 16:38:43 2.0 \n",
"\n",
" trip_distance pickup_x pickup_y RateCodeID \\\n",
"0 0.77 -8232200.852 4980818.134 1.0 \n",
"1 0.74 -8236124.619 4973741.097 1.0 \n",
"2 1.61 -8238354.032 4975721.343 1.0 \n",
"3 0.56 -8233618.334 4977926.822 1.0 \n",
"4 3.85 -8237552.293 4971697.934 1.0 \n",
"... ... ... ... ... \n",
"291542 2.28 -8232027.594 4978811.060 1.0 \n",
"291543 1.36 -8236281.740 4975538.043 1.0 \n",
"291544 1.07 -8231666.642 4980123.266 1.0 \n",
"291545 0.65 -8234554.263 4980903.944 1.0 \n",
"291546 5.20 -8234358.074 4982376.298 1.0 \n",
"\n",
" store_and_fwd_flag dropoff_x dropoff_y payment_type \\\n",
"0 N -8231245.389 4980848.420 2.0 \n",
"1 N -8234966.173 4972970.518 1.0 \n",
"2 N -8236185.769 4976682.745 2.0 \n",
"3 N -8234368.266 4977621.255 1.0 \n",
"4 N -8236852.469 4976189.980 2.0 \n",
"... ... ... ... ... \n",
"291542 N -8234805.656 4976368.247 1.0 \n",
"291543 N -8235851.994 4977687.974 2.0 \n",
"291544 N -8232250.960 4981558.483 2.0 \n",
"291545 N -8235198.032 4979689.770 1.0 \n",
"291546 N -8236800.662 4974834.022 1.0 \n",
"\n",
" fare_amount extra mta_tax tip_amount tolls_amount total_amount \\\n",
"0 5.5 0.5 0.5 0.00 0.0 6.80 \n",
"1 5.5 0.5 0.5 1.00 0.0 7.80 \n",
"2 7.5 0.5 0.5 0.00 0.0 8.80 \n",
"3 6.0 0.0 0.5 1.20 0.0 0.30 \n",
"4 23.0 0.5 0.5 0.00 0.0 24.30 \n",
"... ... ... ... ... ... ... \n",
"291542 15.5 0.0 0.5 2.32 0.0 0.30 \n",
"291543 8.5 0.0 0.5 0.00 0.0 0.30 \n",
"291544 6.5 0.0 0.5 0.00 0.0 7.30 \n",
"291545 4.0 1.0 0.5 1.50 0.0 7.30 \n",
"291546 19.5 1.0 0.5 5.32 0.0 26.62 \n",
"\n",
" pickup_time total_cost \n",
"0 2015-03-07 02:17:05 6.80 \n",
"1 2015-02-05 21:05:35 6.80 \n",
"2 2015-02-04 23:25:27 8.80 \n",
"3 2015-01-25 14:27:24 -0.90 \n",
"4 2015-02-06 23:45:47 24.30 \n",
"... ... ... \n",
"291542 2015-01-07 08:13:59 -2.02 \n",
"291543 2015-01-03 17:42:31 0.30 \n",
"291544 2015-02-28 08:29:29 7.30 \n",
"291545 2015-03-13 16:57:37 5.80 \n",
"291546 2015-02-05 16:19:05 21.30 \n",
"\n",
"[291547 rows x 20 columns]"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"query = '''\n",
" SELECT \n",
" *,\n",
" CAST(tpep_pickup_datetime || '.0' AS timestamp) AS pickup_time, \n",
" total_amount - tip_amount AS total_cost \n",
" FROM \n",
" taxi \n",
" WHERE\n",
" passenger_count <> 1\n",
" '''\n",
"bc.sql(query)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### The above query is nested in this query"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>pickup_time</th>\n",
" <th>passenger_count</th>\n",
" <th>trip_distance</th>\n",
" <th>total_cost</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2015-03-07 02:17:05</td>\n",
" <td>3.0</td>\n",
" <td>0.77</td>\n",
" <td>6.80</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2015-02-05 21:05:35</td>\n",
" <td>2.0</td>\n",
" <td>0.74</td>\n",
" <td>6.80</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2015-02-04 23:25:27</td>\n",
" <td>5.0</td>\n",
" <td>1.61</td>\n",
" <td>8.80</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2015-01-25 14:27:24</td>\n",
" <td>5.0</td>\n",
" <td>0.56</td>\n",
" <td>-0.90</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2015-02-06 23:45:47</td>\n",
" <td>2.0</td>\n",
" <td>3.85</td>\n",
" <td>24.30</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>291542</th>\n",
" <td>2015-01-07 08:13:59</td>\n",
" <td>3.0</td>\n",
" <td>2.28</td>\n",
" <td>-2.02</td>\n",
" </tr>\n",
" <tr>\n",
" <th>291543</th>\n",
" <td>2015-01-03 17:42:31</td>\n",
" <td>2.0</td>\n",
" <td>1.36</td>\n",
" <td>0.30</td>\n",
" </tr>\n",
" <tr>\n",
" <th>291544</th>\n",
" <td>2015-02-28 08:29:29</td>\n",
" <td>5.0</td>\n",
" <td>1.07</td>\n",
" <td>7.30</td>\n",
" </tr>\n",
" <tr>\n",
" <th>291545</th>\n",
" <td>2015-03-13 16:57:37</td>\n",
" <td>3.0</td>\n",
" <td>0.65</td>\n",
" <td>5.80</td>\n",
" </tr>\n",
" <tr>\n",
" <th>291546</th>\n",
" <td>2015-02-05 16:19:05</td>\n",
" <td>2.0</td>\n",
" <td>5.20</td>\n",
" <td>21.30</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>291547 rows × 4 columns</p>\n",
"</div>"
],
"text/plain": [
" pickup_time passenger_count trip_distance total_cost\n",
"0 2015-03-07 02:17:05 3.0 0.77 6.80\n",
"1 2015-02-05 21:05:35 2.0 0.74 6.80\n",
"2 2015-02-04 23:25:27 5.0 1.61 8.80\n",
"3 2015-01-25 14:27:24 5.0 0.56 -0.90\n",
"4 2015-02-06 23:45:47 2.0 3.85 24.30\n",
"... ... ... ... ...\n",
"291542 2015-01-07 08:13:59 3.0 2.28 -2.02\n",
"291543 2015-01-03 17:42:31 2.0 1.36 0.30\n",
"291544 2015-02-28 08:29:29 5.0 1.07 7.30\n",
"291545 2015-03-13 16:57:37 3.0 0.65 5.80\n",
"291546 2015-02-05 16:19:05 2.0 5.20 21.30\n",
"\n",
"[291547 rows x 4 columns]"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"query = '''\n",
" SELECT \n",
" pickup_time, \n",
" passenger_count, \n",
" trip_distance, \n",
" total_cost \n",
" FROM\n",
" (\n",
" SELECT \n",
" *,\n",
" CAST(tpep_pickup_datetime || '.0' AS timestamp) AS pickup_time, \n",
" total_amount - tip_amount AS total_cost \n",
" FROM \n",
" taxi \n",
" WHERE\n",
" passenger_count <> 1\n",
" )\n",
" '''\n",
"bc.sql(query)"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>years</th>\n",
" <th>months</th>\n",
" <th>days</th>\n",
" <th>p_hours</th>\n",
" <th>p_minutes</th>\n",
" <th>p_seconds</th>\n",
" <th>d_hours</th>\n",
" <th>d_minutes</th>\n",
" <th>d_seconds</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2015</td>\n",
" <td>3</td>\n",
" <td>5</td>\n",
" <td>7</td>\n",
" <td>38</td>\n",
" <td>36</td>\n",
" <td>7</td>\n",
" <td>44</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2015</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>5</td>\n",
" <td>41</td>\n",
" <td>55</td>\n",
" <td>5</td>\n",
" <td>46</td>\n",
" <td>54</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2015</td>\n",
" <td>2</td>\n",
" <td>8</td>\n",
" <td>16</td>\n",
" <td>17</td>\n",
" <td>51</td>\n",
" <td>16</td>\n",
" <td>37</td>\n",
" <td>45</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2015</td>\n",
" <td>3</td>\n",
" <td>7</td>\n",
" <td>2</td>\n",
" <td>17</td>\n",
" <td>5</td>\n",
" <td>2</td>\n",
" <td>22</td>\n",
" <td>18</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2015</td>\n",
" <td>2</td>\n",
" <td>5</td>\n",
" <td>21</td>\n",
" <td>5</td>\n",
" <td>35</td>\n",
" <td>21</td>\n",
" <td>10</td>\n",
" <td>33</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>999995</th>\n",
" <td>2015</td>\n",
" <td>2</td>\n",
" <td>23</td>\n",
" <td>22</td>\n",
" <td>14</td>\n",
" <td>26</td>\n",
" <td>22</td>\n",
" <td>17</td>\n",
" <td>34</td>\n",
" </tr>\n",
" <tr>\n",
" <th>999996</th>\n",
" <td>2015</td>\n",
" <td>1</td>\n",
" <td>22</td>\n",
" <td>3</td>\n",
" <td>46</td>\n",
" <td>3</td>\n",
" <td>3</td>\n",
" <td>49</td>\n",
" <td>51</td>\n",
" </tr>\n",
" <tr>\n",
" <th>999997</th>\n",
" <td>2015</td>\n",
" <td>2</td>\n",
" <td>5</td>\n",
" <td>16</td>\n",
" <td>19</td>\n",
" <td>5</td>\n",
" <td>16</td>\n",
" <td>38</td>\n",
" <td>43</td>\n",
" </tr>\n",
" <tr>\n",
" <th>999998</th>\n",
" <td>2015</td>\n",
" <td>1</td>\n",
" <td>27</td>\n",
" <td>18</td>\n",
" <td>29</td>\n",
" <td>36</td>\n",
" <td>18</td>\n",
" <td>40</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>999999</th>\n",
" <td>2015</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>7</td>\n",
" <td>19</td>\n",
" <td>14</td>\n",
" <td>7</td>\n",
" <td>24</td>\n",
" <td>14</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>1000000 rows × 9 columns</p>\n",
"</div>"
],
"text/plain": [
" years months days p_hours p_minutes p_seconds d_hours \\\n",
"0 2015 3 5 7 38 36 7 \n",
"1 2015 2 3 5 41 55 5 \n",
"2 2015 2 8 16 17 51 16 \n",
"3 2015 3 7 2 17 5 2 \n",
"4 2015 2 5 21 5 35 21 \n",
"... ... ... ... ... ... ... ... \n",
"999995 2015 2 23 22 14 26 22 \n",
"999996 2015 1 22 3 46 3 3 \n",
"999997 2015 2 5 16 19 5 16 \n",
"999998 2015 1 27 18 29 36 18 \n",
"999999 2015 3 1 7 19 14 7 \n",
"\n",
" d_minutes d_seconds \n",
"0 44 6 \n",
"1 46 54 \n",
"2 37 45 \n",
"3 22 18 \n",
"4 10 33 \n",
"... ... ... \n",
"999995 17 34 \n",
"999996 49 51 \n",
"999997 38 43 \n",
"999998 40 6 \n",
"999999 24 14 \n",
"\n",
"[1000000 rows x 9 columns]"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"query = '''\n",
" select\n",
" \n",
" year(tpep_pickup_datetime) as years,\n",
" \n",
" month(tpep_pickup_datetime) as months,\n",
" dayofmonth(tpep_pickup_datetime) as days,\n",
" \n",
" hour(tpep_pickup_datetime) as p_hours,\n",
" minute(tpep_pickup_datetime) as p_minutes,\n",
" second(tpep_pickup_datetime) as p_seconds,\n",
" \n",
" hour(tpep_dropoff_datetime) as d_hours,\n",
" minute(tpep_dropoff_datetime) as d_minutes,\n",
" second(tpep_dropoff_datetime) as d_seconds\n",
" \n",
" from \n",
" (\n",
" select tpep_pickup_datetime, tpep_dropoff_datetime from taxi\n",
" )\n",
" '''\n",
"bc.sql(query)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Zillow Data"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>parcelid</th>\n",
" <th>logerror</th>\n",
" <th>transactiondate</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>90273</th>\n",
" <td>11402105</td>\n",
" <td>0.0602</td>\n",
" <td>2016-12-30</td>\n",
" </tr>\n",
" <tr>\n",
" <th>90274</th>\n",
" <td>12566293</td>\n",
" <td>0.4207</td>\n",
" <td>2016-12-30</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" parcelid logerror transactiondate\n",
"90273 11402105 0.0602 2016-12-30\n",
"90274 12566293 0.4207 2016-12-30"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import cudf \n",
"\n",
"df = cudf.read_csv('https://github.com/gumdropsteve/datasets/blob/master/zillow/train_2016_v2.csv?raw=true')\n",
"\n",
"bc.create_table('train_2016', df)\n",
"\n",
"df.tail(2)"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>parcelid</th>\n",
" <th>airconditioningtypeid</th>\n",
" <th>architecturalstyletypeid</th>\n",
" <th>basementsqft</th>\n",
" <th>bathroomcnt</th>\n",
" <th>bedroomcnt</th>\n",
" <th>buildingclasstypeid</th>\n",
" <th>buildingqualitytypeid</th>\n",
" <th>calculatedbathnbr</th>\n",
" <th>decktypeid</th>\n",
" <th>...</th>\n",
" <th>numberofstories</th>\n",
" <th>fireplaceflag</th>\n",
" <th>structuretaxvaluedollarcnt</th>\n",
" <th>taxvaluedollarcnt</th>\n",
" <th>assessmentyear</th>\n",
" <th>landtaxvaluedollarcnt</th>\n",
" <th>taxamount</th>\n",
" <th>taxdelinquencyflag</th>\n",
" <th>taxdelinquencyyear</th>\n",
" <th>censustractandblock</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>99505</th>\n",
" <td>13841611</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>2.0</td>\n",
" <td>4.0</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>2.0</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>...</td>\n",
" <td>1.0</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>109759.0</td>\n",
" <td>332481.0</td>\n",
" <td>2015</td>\n",
" <td>222722.0</td>\n",
" <td>4034.84</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>6.059075e+13</td>\n",
" </tr>\n",
" <tr>\n",
" <th>99506</th>\n",
" <td>13842040</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>1.0</td>\n",
" <td>3.0</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>1.0</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>...</td>\n",
" <td>1.0</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>47101.0</td>\n",
" <td>233509.0</td>\n",
" <td>2015</td>\n",
" <td>186408.0</td>\n",
" <td>2912.74</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>&lt;NA&gt;</td>\n",
" <td>6.059075e+13</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>2 rows × 58 columns</p>\n",
"</div>"
],
"text/plain": [
" parcelid airconditioningtypeid architecturalstyletypeid basementsqft \\\n",
"99505 13841611 <NA> <NA> <NA> \n",
"99506 13842040 <NA> <NA> <NA> \n",
"\n",
" bathroomcnt bedroomcnt buildingclasstypeid buildingqualitytypeid \\\n",
"99505 2.0 4.0 <NA> <NA> \n",
"99506 1.0 3.0 <NA> <NA> \n",
"\n",
" calculatedbathnbr decktypeid ... numberofstories fireplaceflag \\\n",
"99505 2.0 <NA> ... 1.0 <NA> \n",
"99506 1.0 <NA> ... 1.0 <NA> \n",
"\n",
" structuretaxvaluedollarcnt taxvaluedollarcnt assessmentyear \\\n",
"99505 109759.0 332481.0 2015 \n",
"99506 47101.0 233509.0 2015 \n",
"\n",
" landtaxvaluedollarcnt taxamount taxdelinquencyflag \\\n",
"99505 222722.0 4034.84 <NA> \n",
"99506 186408.0 2912.74 <NA> \n",
"\n",
" taxdelinquencyyear censustractandblock \n",
"99505 <NA> 6.059075e+13 \n",
"99506 <NA> 6.059075e+13 \n",
"\n",
"[2 rows x 58 columns]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = cudf.read_csv('https://github.com/gumdropsteve/datasets/blob/master/zillow/properties_2016_part_0.csv?raw=true')\n",
"\n",
"bc.create_table('zillow', df)\n",
"\n",
"df.tail(2)"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>longest_string</th>\n",
" <th>shortest_string</th>\n",
" <th>avgt_string</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>15</td>\n",
" <td>11</td>\n",
" <td>14</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" longest_string shortest_string avgt_string\n",
"0 15 11 14"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"query = '''\n",
" SELECT \n",
" MAX(a) AS longest_string,\n",
" MIN(a) AS shortest_string,\n",
" AVG(a) AS avgt_string\n",
" FROM \n",
" (\n",
" SELECT \n",
" CHAR_LENGTH(censustractandblock) AS a\n",
" FROM\n",
" zillow\n",
" )\n",
" '''\n",
"\n",
"bc.sql(query)"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "RAPIDS Stable",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.8.5"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Display the source blob
Display the rendered blob
Raw
sql_joins.ipynb
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment