Skip to content

Instantly share code, notes, and snippets.

@sebsto
Last active July 25, 2024 15:13
Show Gist options
  • Save sebsto/2ce59d80a3b5d30bb1ee456e96bf0fb0 to your computer and use it in GitHub Desktop.
Save sebsto/2ce59d80a3b5d30bb1ee456e96bf0fb0 to your computer and use it in GitHub Desktop.
Redshift Materialized View Demo
drop table store;
drop table sales;
drop materialized view city_sales;
CREATE TABLE "sales" (
"id" int PRIMARY KEY,
"item" varchar,
"store_id" int,
"customer_id" int,
"amount" numeric
);
CREATE TABLE "store" (
"id" int PRIMARY KEY,
"name" varchar,
"owner" varchar,
"city" varchar
);
ALTER TABLE "sales" ADD FOREIGN KEY ("store_id") REFERENCES "store" ("id");
INSERT INTO public.store
(id, name, owner, city)
VALUES(1, 'Electronic Shop', 'Seb', 'Paris'),
(2, 'Video Land', 'Jeff', 'Seattle'),
(3, 'Photo Click', 'Natalia', 'Paris');
INSERT INTO public.sales
(id, item, store_id, customer_id, amount)
VALUES(1, 'HDMI - Thunderbold adapter', 1, 1, 30),
(2, 'SSD Disk 1Tb', 1, 2, 500),(3, 'Flash Card Reader', 1, 3, 10),
(4, 'HDMI - SDI Mixer Box', 2, 1, 300),(5, '4k Camera', 2, 1, 500),
(6, 'Light Ring', 3, 2, 100),(7, 'UV Filter', 3, 1, 50);
select * from store ;
select * from sales;
SELECT st.city, SUM(sa.amount)
FROM sales sa, store st
WHERE sa.store_id = st.id
GROUP BY st.city;
CREATE MATERIALIZED VIEW city_sales AS (
SELECT st.city, SUM(sa.amount) as total_sales
FROM sales sa, store st
WHERE sa.store_id = st.id
GROUP BY st.city
);
SELECT city,total_sales FROM city_sales;
SELECT city, total_sales FROM city_sales WHERE city = 'Paris';
INSERT INTO sales
(id, item, store_id, customer_id, amount)
VALUES(8, 'Gaming PC Super ProXXL', 1, 1, 3000);
REFRESH MATERIALIZED VIEW city_sales;
SELECT city, total_sales FROM city_sales WHERE city = 'Paris';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment