The avatar for @samchencode
samchencode
Public
ClickHouse Tutorial Series
Edited
Feb 28, 2023
3
ClickHouse Tutorial Series
❶ What Are Databases?
❷ Intro To SQL Syntax
❸ Table Relationships & Joins❹ Aggregation, Subqueries, Windows❺ Creating Tables & Inserting Data❻ Very Briefly: Integration, Optimization, Configuration
Insert cell
Insert cell
client
SELECT * FROM stock LIMIT 10
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT trip_id, pickup_datetime, dropoff_datetime FROM trips
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT * FROM trips
Insert cell
Insert cell
client
SELECT * EXCEPT (trip_id, vendor_id) FROM trips
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT
cab_type, pickup_datetime, trip_distance, fare_amount
FROM trips WHERE
cab_type = 'yellow' AND
pickup_datetime < '2015-01-01'
Insert cell
Insert cell
client
SELECT
fare_amount, dropoff_ntacode, cab_type, trip_distance
FROM trips
WHERE
fare_amount BETWEEN 2 AND 9 AND
dropoff_ntacode LIKE '_N%'
Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT trip_id AS id, vendor_id AS vendor FROM trips;
Insert cell
Insert cell
client
SELECT DISTINCT cab_type FROM trips;
Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT dish_name, price FROM menu_item_denorm ORDER BY price DESC, dish_name ASC
Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT * FROM trips LIMIT 5
Insert cell
Insert cell
client
SELECT * FROM trips LIMIT 5 OFFSET 3
Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT
dish_name,
price
FROM menu_item_denorm
ORDER BY price DESC
LIMIT 5

UNION ALL

SELECT
dish_name,
price
FROM menu_item_denorm
ORDER BY price ASC
LIMIT 5
Insert cell
Insert cell
Insert cell
client
DESCRIBE TABLE recipes
Insert cell
Insert cell
client
SHOW TABLES
Insert cell
client
SHOW DATABASES
Insert cell
Insert cell
client
SELECT
rand(0) AS a,
rand(1) AS b,
a + b AS added,
a - b AS subtracted,
a * b AS multiplied,
a / b AS divided,
a % b AS moded,
abs(subtracted)
FROM numbers(10);
Insert cell
client
SELECT
rand(0) AS a,
rand(1) AS b,
a > b AS gt,
a < b AS lt,
a = b AS eq,
a != b AS ne
FROM numbers(10);
Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT
title,
source,
length(title),
concat(source, '-', trim(title)),
lower(title) LIKE '%chocolate%'
FROM recipes
Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT
now(),
today(),
dateDiff('hour', yesterday(), today()) AS hrs_btn_td_ytd,
today() + INTERVAL 1 day AS tomorrow,
today() - INTERVAL 1 year AS yester_year,
formatDateTime(now(), '%D at %I:%M:%S %p', 'EST')
Insert cell
Insert cell
Insert cell
client
SELECT
number,
[1,5,2,3,2,4,5,1] AS arr,
empty(arr),
length(arr),
has(arr, number),
indexOf(arr, number),
arrayDistinct(arr)
FROM numbers(10) -- numbers 0 thru 9
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT
CAST(1234 AS String) AS my_string,
CAST('1970-01-01' AS Date) AS my_date,
CAST('1899-01-01' AS Date) AS my_wrong_date,
CAST(0 AS BOOLEAN) AS my_false,
CAST(1 AS BOOLEAN) AS my_true

Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT number FROM numbers(10)
Insert cell
Insert cell
client
SELECT
title,
length(ingredients) > 3 ? 'four or more ingredients' : 'three or less ingredients' AS category,
CASE
WHEN source ='Gathered' THEN true
ELSE false
END AS was_gathered
FROM recipes;
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more