Новичок вопрос о SQL: арифметика с несколькими РАССЧИТЫВАЕТ (*) результаты
Продолжая дух использования Проводника Данных Exchange Стека для изучения SQL, (см.: мы можем стать нашим собственным “Northwind” для обучающего SQL / базы данных?), я решил попытаться записать запрос для ответа на простой вопрос (на meta): у Каких % stackoverflow пользователей есть более чем 10 000 представителей?.
Вот то, что я сделал:
Query#1
SELECT COUNT(*)
FROM Users
WHERE
Users.Reputation >= 10000
Результат:
556
Query#2
SELECT COUNT(*)
FROM
USERS
Результат:
227691
Теперь, как я соединяю их в один запрос? Чем называют эту идиому запроса? Что делает я должен записать, таким образом, я могу получить, скажем, результат на три столбца с одной строкой как это:
556 227691 0,00244190592
6 ответов
Вы можете использовать Common Table Expression (CTE) :
WITH c1 AS (
SELECT COUNT(*) AS cnt
FROM Users
WHERE Users.Reputation >= 10000
), c2 AS (
SELECT COUNT(*) AS cnt
FROM Users
)
SELECT c1.cnt, c2.cnt, CAST(c1.cnt AS FLOAT) / c2.cnt
FROM c1, c2
Помимо использования CTEs, в этом случае вы также могли бы сделать:
SELECT CAST((SELECT COUNT(*) FROM Users WHERE Users.Reputation >= 10000) AS float) /
(SELECT COUNT(*) FROM USERS) * 100 AS Percentage
Приведение к float было сделано для принудительного деления с плавающей точкой, потому что при целочисленном делении 556 / 227691 дало бы 0.
Благодаря другим ответам здесь я написал следующие запросы, все из которых работают в SEDE:
«Встроенное представление»
SELECT *, CAST([10K] AS FLOAT)/[All] AS [Ratio]
FROM (
SELECT
(SELECT COUNT(*) FROM Users) AS [All],
(SELECT COUNT(*) FROM Users Where Reputation >= 10000) AS [10K]
) AS UsersCount
Переменные
DECLARE @numAll FLOAT
DECLARE @num10kers FLOAT
SET @numAll = (SELECT COUNT(*) FROM Users)
SET @num10kers = (SELECT COUNT(*) FROM Users WHERE Users.Reputation >= 10000);
SELECT @num10kers AS [10K], @numAll AS [All], @num10Kers/@numAll AS [Ratio]
Ссылки
Выражение общей таблицы
WITH Users10K AS (
SELECT COUNT(*) AS Count
FROM Users
WHERE Users.Reputation >= 10000
), UsersAll AS (
SELECT COUNT(*) As Count
FROM Users
)
SELECT
Users10K.Count AS [10K],
UsersAll.Count AS [All],
CAST(Users10K.Count AS FLOAT) / UsersAll.Count AS [Ratio]
FROM Users10K, UsersAll
Ссылки
WITH tmp as (
SELECT COUNT(ID) AS repCount, (SELECT COUNT(ID) FROM Users ) AS totalCount
FROM Users
WHERE Users.Reputation > 10000
)
SELECT tmp.repCount, tmp.totalCount, (cast(tmp.repCount as decimal(10,2))/tmp.TotalCount) * 100 AS Percentage
FROM tmp
ОБНОВЛЕНО: без with
SELECT COUNT(ID) AS repCount, (SELECT COUNT(ID) FROM Users ) AS totalCount,
(CAST((SELECT COUNT(ID) FROM Users WHERE Users.Reputation > 10000) AS DECIMAL(10,2)) /
(SELECT COUNT(ID) FROM Users )) * 100 AS Persantage
FROM Users
Использование переменных в MySQL:
SELECT @a:=(SELECT COUNT(*) FROM Users WHERE Users.Reputation >= 10000),
@b:=(SELECT COUNT(*) FROM Users),
IF(@b > 0, @a/@b, "--invalid--")
FROM Users
LIMIT 0,1
Для таких запросов, как этот, где я делаю несколько подсчетов в одной таблице на основе различных критериев, мне нравится использовать SUM и CASE:
SELECT
UsersCount.[10K],
UsersCount.[All],
(CAST(UsersCount.[10K] AS FLOAT) / UsersCount.[All]) AS [Ratio]
FROM
(SELECT
SUM(CASE
WHEN Users.Reputation >= 10000 THEN 1
ELSE 0
END) AS [10K],
COUNT(*) AS [All]
FROM Users) AS UsersCount
Преимущество в том, что вы сканируете таблицу Users только один раз, что может быть значительно быстрее.