notme
Picopat
- Katılım
- 5 Kasım 2023
- Mesajlar
- 6
Daha fazla
- Cinsiyet
- Erkek
Merhaba, okul gereği SQL data warehouse ödevimde koduma model clause kısmını eklemem gerekiyor fakat SQL deneyimim çok olmadığı için yapamadım yardımcı olursanız çok sevinirim. ( not : livesql'de kodu yazıyoruz)
SQL:
select
"ID",
"ATHLETE_ID",
"GAME_ID",
"NATION_ID"
from OLYM."OLYM_ATHLETE_GAMES";
select
"ID",
"ATHLETE_NAME",
"ATHLETE_GENDER"
from OLYM."OLYM_ATHLETES";
select
"ID",
"DISCIPLINE_ID",
"EVENT"
from OLYM."OLYM_BASE_EVENTS";
select
"ID",
"SPORT_ID",
"DISCIPLINE"
from OLYM."OLYM_DISCIPLINES";
select
"ID",
"BASE_EVENT_ID",
"EVENT_GENDER"
from OLYM."OLYM_EVENTS";
select
"ID",
"YEAR",
"SEASON",
"CITY"
from OLYM."OLYM_GAMES";
select
"ID",
"EVENT_ID",
"ATHLETE_GAME_ID",
"MEDAL"
from OLYM."OLYM_MEDALS";
select
"ID",
"NATION"
from OLYM."OLYM_NATIONS";
select
"ID",
"SPORT"
from OLYM."OLYM_SPORTS";
select
"CITY",
"EDITION",
"SPORT",
"DISCIPLINE",
"ATHLETE",
"NOC",
"GENDER",
"EVENT",
"EVENT_GENDER",
"MEDAL"
from OLYM."OLYM_MEDALS_VIEW";
-- Query 1: ROLLUP to summarize "YEAR", "SPORT", and "MEDAL"
SELECT
"ATHLETE",
"SPORT",
"MEDAL",
COUNT() AS "Medal_Count"
FROM OLYM."OLYM_MEDALS_VIEW"
GROUP BY ROLLUP("ATHLETE", "MEDAL", "SPORT");
-- Query 2: CUBE to summarize all possible possible combination of the total medals
SELECT
"SPORT",
"MEDAL",
"GENDER",
COUNT() AS "MedalCount"
FROM OLYM."OLYM_MEDALS_VIEW"
GROUP BY CUBE ("SPORT", "MEDAL", "GENDER");
-- Query 3: OVER, groups within the result set based on the "ATHLETE"
SELECT
DISTINCT "ATHLETE",
COUNT(*) OVER(PARTITION BY "ATHLETE") AS "Total_Medals_Won" -- calculated independently for each athlete.
FROM OLYM."OLYM_MEDALS_VIEW"
WHERE "MEDAL" IS NOT NULL
ORDER BY "ATHLETE";
-- Query 4: Select the city, sport, and total medals won by the city in each sport.
SELECT
"CITY" AS "City",
"SPORT" AS "Sport",
COUNT(*) AS "Total_Medals_Won" -- Count the total number of medals won by the city in the sport.
FROM OLYM."OLYM_MEDALS_VIEW"
WHERE "MEDAL" IS NOT NULL
GROUP BY "CITY", "SPORT"
ORDER BY "City", "Sport";
-- Query 5: ROLLUP Select the sport, medal type, medal count, and average medals per sport.
SELECT
"SPORT",
"MEDAL",
COUNT() AS "Medal_Count", -- Count the number of medals for each sport and medal type.
AVG(COUNT()) OVER(PARTITION BY "SPORT") AS "Average_Medals" -- Calculate the average number of medals per sport.
FROM OLYM."OLYM_MEDALS_VIEW"
GROUP BY ROLLUP("SPORT", "MEDAL")
HAVING GROUPING_ID("SPORT") = 0; -- Filter the results to keep the grand total row for sport.
-- Query 6: Select the sport, gender, medal type, medal count, and percentage of total medals.
SELECT
"SPORT",
"GENDER",
"MEDAL",
COUNT() AS "MedalCount",
(COUNT() / SUM(COUNT()) OVER()) 100 AS "Percentage_Of_Total_Medals" -- Calculate the percentage of medals in each combination.
FROM OLYM."OLYM_MEDALS_VIEW"
GROUP BY CUBE ("SPORT", "GENDER", "MEDAL");
