-
Notifications
You must be signed in to change notification settings - Fork 42
/
Copy pathbigwide_vs_tree.malloynb
81 lines (80 loc) · 2.42 KB
/
bigwide_vs_tree.malloynb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
>>>markdown
# Big Wide Table vs Tree
SQL (and LookML) present joined data as a _Big Wide Table_. Each alias joined tables is prestented as an alias.
>>>sql
\>>>sql
SELECT
*
FROM 'data/ecommerce/orders.parquet' as orders
LEFT JOIN 'data/ecommerce/items.parquet' as items
ON orders.order_id=items.order_id
LEFT JOIN 'data/ecommerce/products.parquet' as products
ON items.product_id = products.product_id
LEFT JOIN 'data/ecommerce/users.parquet' as users
ON orders.user_id = users.user_id
GROUP BY 1
order by 2 desc
>>>sql
\>>>sql
SELECT
products.brand,
count(distinct orders.order_id) as order_count,
count(distinct items.item_id) as item_count,
sum(items.sale_price) as total_sales,
count(distinct users.user_id) as user_count
FROM 'data/ecommerce/orders.parquet' as orders
LEFT JOIN 'data/ecommerce/items.parquet' as items
ON orders.order_id=items.order_id
LEFT JOIN 'data/ecommerce/products.parquet' as products
ON items.product_id = products.product_id
LEFT JOIN 'data/ecommerce/users.parquet' as users
ON orders.user_id = users.user_id
GROUP BY 1
order by 2 desc
limit 10
>>>malloy
source: products is duckdb.table('data/ecommerce/products.parquet')
source: users is duckdb.table('data/ecommerce/users.parquet')
source: inventory_items is duckdb.table('data/ecommerce/inventory_items.parquet')
source: items is duckdb.table('data/ecommerce/items.parquet') {
join_one: products on product_id = products.product_id
join_one: inventory_items on inventory_item_id = inventory_items.inventory_item_id
}
source: orders is duckdb.table('data/ecommerce/orders.parquet') {
join_many: items on order_id = items.order_id
join_one: users on user_id = users.user_id
}
>>>malloy
run: orders -> {
group_by:
items.products.brand
aggregate:
order_count is count()
item_count is items.count()
total_sales is items.sale_price.sum()
user_count is users.count()
limit: 10
}
>>>malloy
run: orders -> {
group_by:
items.products.brand
items.products.category
aggregate:
order_count is count()
item_count is items.count()
total_sales is items.sale_price.sum()
user_count is users.count()
average_age is users.age.avg()
average_cost is items.inventory_items.cost.avg()
limit: 10
}
>>>malloy
run: orders -> {
group_by: order_month is created_at.month
aggregate:
order_count is count()
item_count is items.count()
total_sales is items.sale_price.sum()
user_count is users.count()
}