Standard GROUP BY
Select brand, sum(sales) from SalesTable group by brand
Everlane 20)
What if we wanted to group by different combinations of attributes?
Copyright By PowCoder代写 加微信 powcoder
CREATE TABLE ag_class_sales (
country text, product_name text, year int, amount_sold numeric
INSERT INTO ag_class_sales VALUES (‘Argentina’, ‘Shoes’, 2020, 12), (‘Argentina’, ‘Shoes’, 2021, 14), (‘Argentina’, ‘Hats’, 2020, 54), (‘Argentina’, ‘Hats’, 2021, 57), (‘Germany’, ‘Shoes’, 2020, 34), (‘Germany’, ‘Shoes’, 2021, 29), (‘Germany’, ‘Hats’, 2020, 19), (‘Germany’, ‘Hats’, 2021, 22), (‘USA’, ‘Shoes’, 2020, 99),
(‘USA’, ‘Shoes’, 2021, 103), (‘USA’, ‘Hats’, 2020, 81), (‘USA’, ‘Hats’, 2021, 90)
SELECT country, product_name, sum(amount_sold) FROM ag_class_sales
GROUP BY GROUPING SETS ((1), (2))
ORDER BY 1, 2;
SELECT country, product_name, sum(amount_sold) FROM ag_class_sales
GROUP BY ROLLUP (1, 2)
ORDER BY 1, 2;
SELECT country, product_name, year, sum(amount_sold) FROM ag_class_sales
GROUP BY ROLLUP (1, 2, 3)
ORDER BY 1, 2, 3;
No grouping by product_name
SELECT CASE WHEN country IS NULL
THEN ‘Product Total’ ELSE country
CASE WHEN product_name IS NULL
THEN ‘Country Total’ ELSE product_name END,
sales FROM (
SELECT country, product_name, sum(amount_sold) as sales
FROM ag_class_sales GROUP BY CUBE (1, 2) ORDER BY 1, 2
SELECT CASE WHEN country IS NULL
THEN ‘Product Total’ ELSE country
CASE WHEN product_name IS NULL
THEN ‘Country Total’ ELSE product_name END,
sales FROM (
SELECT country, product_name, sum(amount_sold) as sales FROM ag_class_sales
WHERE amount_sold < 50
GROUP BY CUBE (1, 2)
ORDER BY 1, 2 ) a;
SELECT CASE WHEN country IS NULL
THEN 'Product Total' ELSE country
CASE WHEN product_name IS NULL
THEN 'Country Total' ELSE product_name END,
sales FROM (
SELECT country, product_name, sum(amount_sold) as sales FROM ag_class_sales
GROUP BY CUBE (1, 2)
HAVING sum(amount_sold) < 50
ORDER BY 1, 2 ) a;
SELECT 100 * FLOOR(conv_price(weekly_price) / 100) as price_range, SUM(CASE WHEN place = 'Pleasant Plains' THEN 1 ELSE 0 END) as PP FROM "Airbnb_listings" a, (
select id, trim(regexp_split_to_table(neighborhood, E'[,]')) as place
from "Airbnb_listings" ) neighborhoods
WHERE weekly_price is not null and neighborhoods.id = a.id
GROUP BY 100 * FLOOR(conv_price(weekly_price) / 100) ORDER BY price_range;
Group By Reduces the Number of Rows Partition Over Retains the identity of Rows
SELECT zip,
AVG (conv_price(weekly_price)) OVER (PARTITION BY zip)
FROM "Airbnb_listings" A,
select id, unnest(regexp_match(street,'([0-9]{5})')) as zip from "Airbnb_listings"
WHERE zips.id = A.id and
A.weekly_price is not null order by zip
SELECT zip, amenities[1],
AVG (conv_price(weekly_price)) OVER (PARTITION BY zip order by amenities[1])
FROM "Airbnb_listings" A,
select id, unnest(regexp_match(street,'([0-9]{5})')) as zip from "Airbnb_listings"
WHERE zips.id = A.id and
A.weekly_price is not null order by zip
SELECT zip, (100 * FLOOR(conv_price(price) / 100)) as bracket,
AVG (conv_price(weekly_price)) OVER (PARTITION BY zip order by (100 * FLOOR(conv_price(price) / 100)))
FROM "Airbnb_listings" A,
select id, unnest(regexp_match(street,'([0-9]{5})')) as zip from "Airbnb_listings"
WHERE zips.id = A.id and
A.weekly_price is not null order by zip
select id, conv_price(price),
rank() over (order by conv_price(price) desc nulls last) as rank, dense_rank() over (order by conv_price(price) desc nulls last) as denserank, row_number()over (order by conv_price(price) desc nulls last) as rownum
from "Airbnb_listings";
Same-valued ordering variable alters ranking
SELECT zip, (100 * FLOOR(conv_price(weekly_price) / 100)) as bracket,
rank() over (order by (100 * FLOOR(conv_price(weekly_price) / 100))) as rank1,
rank() over (partition by zip order by (100 * FLOOR(conv_price(weekly_price) / 100))) as rank2
FROM "Airbnb_listings" A,
select id, unnest(regexp_match(street,'([0-9]{5})')) as zip from "Airbnb_listings"
WHERE zips.id = A.id and
A.weekly_price is not null order by zip;
SELECT zip, avg(conv_price(weekly_price)) as avg_price,
rank() over (order by avg(conv_price(weekly_price))) as rank_avg
FROM "Airbnb_listings" A,
select id, unnest(regexp_match(street,'([0-9]{5})')) as zip from "Airbnb_listings"
WHERE zips.id = A.id and
A.weekly_price is not null group by zip
order by rank_avg;
Data Table: Registration
We want to compute a separate cumulative sum of registered users for each country
SELECT country, registration_date, registred_users, SUM(registred_users)OVER (
PARTITION BY country
ORDER BY registration_date)
AS total_users
FROM registration;
SELECT h.*
FROM hpi_master h WHERE random() < 0.01
FROM hpi_master TABLESAMPLE SYSTEM (1);
FROM hpi_master TABLESAMPLE BERNOULLI (1);
Sampling of pages
Sampling of records
select m.fund_symbol, size_type, investment_type,
row_number() over (partition by investment_type,size_type order by random()) as seqnum, count(*) over (partition by investment_type,size_type) as cnt
from mutualfunds m
where fund_price_earning_ratio > 0;
with s as (
select m.fund_symbol, size_type, investment_type,
row_number() over (partition by investment_type,size_type order by random()) as seqnum,
count(*) over (partition by investment_type,size_type) as cnt from mutualfunds m
where fund_price_earning_ratio > 0
order by cast(seqnum as float) / cnt limit 500
with t as (with s as (
select m.fund_symbol, size_type, investment_type,
row_number() over (partition by investment_type,size_type order by random()) as seqnum,
count(*) over (partition by investment_type,size_type) as cnt from mutualfunds m
where fund_price_earning_ratio > 0
order by cast(seqnum as float) / cnt
limit 500)
select size_type, investment_type, count(*) as groupcount from t
group by size_type, investment_type
2/1/2022 28
with s as (
select m.fund_symbol, size_type, investment_type,
row_number() over (partition by investment_type,size_type order by random()) as seqnum, count(*) over (partition by investment_type,size_type) as p_cnt,
count(*) over () as total_cnt
from mutualfunds m
where fund_price_earning_ratio > 0 )
where seqnum < 500 * (p_cnt * 1.0 / total_cnt)
2/1/2022 30
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com