SQL集約関数の回答

集約関数の問題を解く

<例>HackerRank:Revision Aggregations-The Count Function

[質問]
Query a count of the number of cities in CITY having a Population larger than 100,000.

[回答]

SELECT COUNT(*)
FROM city
WHERE population > 100000;

ソース:https://www.hackerrank.com/challenges/revising-aggregations-the-count-function/problem

<例>HackerRank:Revising Aggregations-Averages

[質問]
Query the average population of all cities in CITY where District is California.

[回答]

SELECT AVG(population)
FROM city
WHERE district = 'California';

ソース:https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem

＜例＞HackerRank：Average Poulation

[質問]
Query the average population for all cities in CITY, rounded down to the nearest integer.

[回答]

SELECT FLOOR(AVG(population))
FROM city;

ソース:https://www.hackerrank.com/challenges/average-population/problem

<例>HackerRank:Revision Aggregations-The Sum Function

[質問]
Query the total population of all cities in CITY where District is California.

[回答]

SELECT SUM(population)
FROM city
WHERE district = 'California';

ソース:https://www.hackerrank.com/challenges/revising-aggregations-sum/problem

<例>HackerRank:Weather観測状態15

[質問]
Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345. Round your answer to decimal places.
[回答]

SELECT ROUND(LONG_W, 4)
FROM station
WHERE LAT_N = (SELECT MAX(LAT_N) FROM station WHERE LAT_N < 137.2345)

𘅾▼注意:WHERE節で直接集約関数を使用することはできません.GROUP BYおよびHAVINGに集約関数を書き込むことも、WHEREセクションのサブクエリで集約関数を使用することもできます.
ソース:https://www.hackerrank.com/challenges/weather-observation-station-15/problem

<例>ドメイン名のテスト:地域販売会社

[質問]
An insurance company maintains records of sales made by its employees. Each employee is assigned to a state. States are grouped under regions. The following tables contain the data:

TABLE regions
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL

TABLE states
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL
  regionId INTEGER NOT NULL REFERENCES regions(id)

TABLE employees
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL
  stateId INTEGER NOT NULL REFERENCES states(id)

TABLE sales
  id INTEGER PRIMARY KEY
  amount INTEGER NOT NULL
  employeeId INTEGER NOT NULL REFERENCES employees(id)  

Management requires a comparative region sales analysis report.
Write a query that returns:

The region name.

Average sales per employee for the region (Average sales = Total sales made for the region/Number of employees in the region).

The difference between the average sales of the region with the highest average sales, and the average sales per employee for the region (average sales to be calculated as explained above).

A region with no sales should be also returned. Use 0 for average sales per employee for such a region when calculating the 2nd and the 3rd column.
[回答]

WITH c AS (SELECT r.name AS regionName
      ,(CASE WHEN SUM(s.amount) IS NOT NULL THEN SUM(s.amount) / COUNT(DISTINCT e.id) ELSE 0 END) AS averageSales
FROM regions AS r
LEFT JOIN states AS st ON r.id = st.regionId
LEFT JOIN employees AS e ON st.id = e.stateId
LEFT JOIN sales AS s ON e.id = s.employeeId
GROUP BY r.name)
SELECT regionName
      , averageSales
      , (SELECT MAX(averageSales) FROM c) - averageSales AS difference
FROM c

[結果]
regionNameaverageSalesdifferenceEast12002800Midwest04000North25001500South40000West24001600
<注意事項>

Average Saless=Total Salessは、地域/地域内の従業員番号として定義されます.AVG関数は使用できません.地域販売の合計を要求し、その地域の総従業員数に分けます.従業員の場合、重複する場合がありますので、従業員数を計算する際にCOUNT DISTINTを使用します.

のすべての地域では、平均販売の最低価格から各地域の平均販売を差し引く.このため、CTEに「平均販売」列を作成し、使用を参照します.

の平均販売の最低価格を求める場合、SELECT内で再度SELECTを使用し、SELECT MAX(平均販売)FROMCに導入する.SELECT内でSELECTを使用せず、そのままMAXを使用すると、結果は1行に戻ります.

**を参照
[コード]

WITH c AS (SELECT r.name AS regionName
      ,(CASE WHEN SUM(s.amount) IS NOT NULL THEN SUM(s.amount) / COUNT(DISTINCT e.id) ELSE 0 END) AS averageSales
FROM regions AS r
LEFT JOIN states AS st ON r.id = st.regionId
LEFT JOIN employees AS e ON st.id = e.stateId
LEFT JOIN sales AS s ON e.id = s.employeeId
GROUP BY r.name)
SELECT regionName
      , averageSales
      , MAX(averageSales) - averageSales AS difference
FROM c

[結果]
regionNameaverageSalesdifferenceSouth40000
ソース:https://www.testdome.com/questions/sql/regional-sales-comparison/36141