Как последовательно считать в SQL все, что больше или равно самому себе?
Скажем, если у меня есть таблица, содержащая идентификаторы оборудования для каждого типа оборудования и возраста оборудования, как я могу сделать Count Distinct идентификаторов оборудования, которые имеют по крайней мере этот возраст оборудования.
Например, предположим, что это все данные, которые у нас есть:
| Тип оборудования | id_оборудования | Equipment_age |
|---|---|---|
| Отвертка | А123 | 1 |
| Отвертка | А234 | 2 |
| Отвертка | А345 | 2 |
| Отвертка | А456 | 2 |
| Отвертка | А567 | 3 |
Я хотел бы, чтобы вывод был:
| Тип оборудования | Equipment_age | count_of_equipment_at_least_this_age |
|---|---|---|
| Отвертка | 1 | 5 |
| Отвертка | 2 | 4 |
| Отвертка | 3 | 1 |
Причина в том, что есть 5 отверток, которым не менее 1 дня, 4 отвертки, которым не менее 2 дней, и только 1 отвертка, которым не менее 3 дней.
До сих пор мне удавалось подсчитывать только оборудование, подпадающее под каждое оборудование_возраст (как этот запрос, показанный ниже), но не «по крайней мере, это оборудование_возраст».
SELECT
equipment_type,
equipment_age,
COUNT(DISTINCT equipment_id) as count_of_equipments
FROM equipment_table
GROUP BY 1, 2
Ответы 3
Используйте подход с самостоятельным соединением:
SELECT
e1.equipment_type,
e1.equipment_age,
COUNT(*) AS count_of_equipments
FROM equipment_table e1
INNER JOIN equipment_table e2
ON e2.equipment_type = e1.equipment_type AND
e2.equipment_age >= e1.equipment_age
GROUP BY 1, 2
ORDER BY 1, 2;
Таким образом, результат вернулся, но count_of_equipments оказался намного больше, чем правильные значения. Интересно, сделал ли я что-то неправильно в своем синтаксисе - я перепроверю.
Есть ли в вашей таблице более одного типа снаряжения, и если да, хотите ли вы указать каждую комбинацию типа и возраста? Или вам нужна только одна запись для каждого возраста?
Да. Мой стол имеет 2 типа оборудования. Я хотел бы сообщить о каждой комбинации тип-возраст. Может быть, я могу попробовать COUNT(DISTINCT e2.equipment_id) вместо COUNT(*).
Это не должно быть необходимо или иметь какое-либо значение.
GROUP BY ограничивает область действия COUNT строками в группе, т. е. не позволит вам достичь других строк (строки с оборудованием_возраста больше, чем у текущей группы). Поэтому вам нужны подзапросы или оконные функции, чтобы получить их. В одну сторону:
SELECT
equipment_type,
equipment_age,
(Select COUNT(*)
from equipment_table cnt
where cnt.equipment_type = a.equipment_type
AND cnt.equipment_age >= a.equipment_age
) as count_of_equipments
FROM equipment_table a
GROUP BY 1, 2, 3
Однако я не уверен, поддерживает ли ваша среда этот синтаксис. Если нет, дайте нам знать, мы найдем другой способ.
Рассмотрим ниже решение без соединения
select distinct
equipment_type,
equipment_age,
count(*) over equipment_at_least_this_age as count_of_equipment_at_least_this_age
from equipment_table
window equipment_at_least_this_age as (
partition by equipment_type
order by equipment_age
range between current row and unbounded following
)
если применяется к выборочным данным в вашем вопросе - вывод
если у меня есть несколько типов оборудования, можно ли настроить запрос для правильного подсчета для каждого типа оборудования?
Конечно. ответ выше должен работать для нескольких типов. ты пытался? Я только что дважды проверил - и это работает для нескольких типов!
Спасибо, @Tim, логика самостоятельного соединения имеет смысл. Знаете ли вы, есть ли более эффективный способ добиться того же результата? Я попробовал подход с самостоятельным присоединением, но для запуска требуется вечность.