データ結合・加工 – Datadeckを最大限に活用するためのSQL基礎(英文)

Do you have “no” knowledge of SQL?
Think SQL is hard?

Well, if you want to use Datadeck’s new data blending feature you will need to know some basic SQL. However, if you don’t, we got you covered.

In this article I’m showing you how easy SQL and I’m including a SQL Cheat Sheet so you don’t have to remember anything just copy – paste

Datadeck now has the power to blend data from multiple data sources into one chart/card and because of that, it’s time to improve your basic SQL query skills to make the most out of this new feature.

For this sample I’d like to use the Tableau – Superstore Demo spreadsheet. (If you want to practice, download it and upload it into your Datadeck account)

Let’s start with some basics.

What is SQL?

SQL stands for Structured Query Language, which is the language we use in our Data blending feature. SQL is used to talk with tables and data sources.

What is a SQL query?

A SQL query or SQL statement is nothing more than a sentence we use to talk to Datadeck and let it get the data we need. So think about like this… “Hey Datadeck, get me the latest sales from the superstore excel file for Toy cars.”

This would be plain English but in a SQL query it’s actually not very different.

SQL Basics

We always start with:

SELECT
FROM

We know both of these words. SELECT will represent the actual result you would like to see. In the sample I used SELECT Sales.

FROM is referring to the column of the source… so FROM Orders.

The full SQL query will look like this:
SELECT Sales FROM Orders

Here you go! You just created your first query.

In my Sample I also used November 2018. Adding that is actually quite easy. For that we can use WHERE

WHERE Productname = "Toy cars"

The new SQL query will be:
SELECT Sales FROM Orders WHERE Productname = "Toy cars"

Datadeck’s SQL Cheat Sheet

We created an overview of some easy to use to SQL queries that will help you make the most out of our Data Blending feature in Datadeck. Feel free to ask us for help or submit some extra queries we can share with our users.

Select all rows in “Orders” table, include all fields

SELECT * FROM Orders

Select all rows in “Orders” table, sort by “Order Date” field

SELECT * FROM Orders ORDER BY Orders.`Order Date`

Select all rows in “Orders” table, reversely sort by “Sales” field

SELECT * FROM Orders ORDER BY Orders.Sales DESC

Select rows in “Orders” table where “Sales” field value is greater than 1000, include selected fields

SELECT Orders.`Order ID`, Orders.`Order Date`, Orders.Sales FROM Orders WHERE Orders.Sales > 1000

Select rows in “Orders” table and add a calculated field

SELECT Orders.*, (Orders.Sales / Orders.Discount) AS BeforeDiscount FROM Orders

Group rows in “Orders” table by “Country” field, include sum of sales and profit

SELECT Orders.Country, SUM(Orders.Sales) AS TotalSales, SUM(Orders.Profit) AS TotalProfit FROM Orders GROUP BY Orders.Country

Join “Orders” table and “Returns” table by matching on “Order ID” field, only keep rows that exist in both tables

SELECT Orders.* FROM Orders INNER JOIN Returned ON Orders.`Order ID` = Returned.`Order ID`

Join “Orders” table and “Returns” table by matching on “Order ID” field, keep all rows that exist in the “Orders” table

SELECT Orders.*, Returned.Returned FROM Orders LEFT JOIN Returned ON Orders.`Order ID` = Returned.`Order ID`

Everything together: group all returned orders by country and get total sales and profit with sorting

SELECT Orders.Country, SUM(Orders.Sales) AS TotalSales, SUM(Orders.Profit) AS TotalProfit FROM Orders INNER JOIN Returned ON Orders.`Order ID` = Returned.`Order ID` GROUP BY Orders.Country ORDER BY TotalSales DESC

Robin Zheng

Ptmind Group 創業者&CEO(Chief Everything Officer)、2010年大学3年生の時にPtmind創業、Data、Digital、SaaS業界マニア(マフィア)(に目指したい)、好奇心と野望は誰も負けず、何も知らず、何も恐れず今まで進んできた。信条は人間の可能性は無限大。(笑)

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です