Travis Tang - Travis Tang (Page 2)

SQL

CUBE+GROUP BY for Subtotals

We all have used GROUP BY, but do you know CUBE? For example, if I have a table of user spending, I can get the subtotal of each user AND the total spend of all users combined, all within one table. 📌 Syntax is here. 𝚂𝙴𝙻𝙴𝙲𝚃 𝚞𝚜𝚎𝚛_𝚒𝚍, 𝚂𝚄𝙼(𝚜𝚙𝚎𝚗𝚍) 𝚊𝚖𝚝 𝙵𝚁𝙾𝙼 𝚞𝚜𝚎𝚛𝚜 𝙶𝚁𝙾𝚄𝙿 𝙱𝚈 𝙲𝚄𝙱𝙴(𝚞𝚜𝚎𝚛_𝚒𝚍) This saves you the need to

Python

Vaex: Using an Out-of-Core Approach to Process Large Datasets

Faster big-data analysis workflows with Vaex

SQL

Get subtotals with ROLLUP+GROUP BY

𝙍𝙊𝙇𝙇𝙐𝙋 generates extra rows that represent the subtotals. For example… To get the sum of spending of each user, you typically GROUP BY user_id and SUM spend. Want to get the sum of ALL users in the same result? Try ROLLUP. 📌 The syntax is here. 𝚂𝙴𝙻𝙴𝙲𝚃 𝚄𝚜𝚎𝚛_𝚒𝚍, 𝚂𝚄𝙼(𝚜𝚙𝚎𝚗𝚍) 𝙵𝚁𝙾𝙼 𝚄𝚜𝚎𝚛𝚜 𝙶𝚁𝙾𝚄𝙿 𝙱𝚈 𝚁𝙾𝙻𝙻𝚄𝙿(𝚞𝚜𝚎𝚛_𝚒𝚍) The result will contain a

SQL

CROSS JOIN for all combinations

It is the cartesian products of both tables, if you're a math nerd. 📌 Syntax: 𝚂𝙴𝙻𝙴𝙲𝚃 * 𝙵𝚁𝙾𝙼 𝚝𝚊𝚋𝚕𝚎_𝟷 𝙲𝚁𝙾𝚂𝚂 𝙹𝙾𝙸𝙽 𝚝𝚊𝚋𝚕𝚎_𝟸 Note that there is no need for an ON clause. CROSS JOIN is useful for getting all combinations - Matching users with all dates. - Match product tables with color tables to get product

SQL

KNN with SQL

Here's a quick implementation of supervised learning, the k-nearest neighbor (KNN) classification algorithm. To get you up to speed, the premise of KNN is that neighboring data points are similar. Thus, we infer the characteristics of an unlabeled point based on its neighbors. 👇 In the code below, I