Как составить запрос чтобы посчитать открывающиеся и закрывающиеся компании?

Question

[beduin01]

Мне нужно посчитать сколько компаний открывается и закрывается каждый год.
В результате у меня должна появиться табличка вида:
date | opened_count | terminate_count

К примеру:
2011 4 5
2012 1 6

И у меня что-то не получается это в один запрос сделать.

Вот структура моей БД:

CREATE table COMPANIES (id int, name VARCHAR(20), reg_date Date, terminate_date Date); 
INSERT INTO COMPANIES VALUES (1, 'Apple', '2000-01-01', null);
INSERT INTO COMPANIES VALUES (2, 'Microsoft', '2001-04-11', null);
INSERT INTO COMPANIES VALUES (3, 'Sony', '2002-11-26', null);
INSERT INTO COMPANIES VALUES (4, 'Zyxel', '2004-04-23', null);
INSERT INTO COMPANIES VALUES (5, 'Dell', '2002-04-17', null);
INSERT INTO COMPANIES VALUES (6, 'SpaseX', '2003-05-22', null);

INSERT INTO COMPANIES VALUES (7, 'Gazprom', '2003-05-22', '2013-04-14');
INSERT INTO COMPANIES VALUES (8, 'Avtovaz', '2003-08-12', '2013-01-27');
INSERT INTO COMPANIES VALUES (9, 'Lukoil', '2004-08-12', '2014-09-13');
INSERT INTO COMPANIES VALUES (10, 'Azbuka', '2003-08-12', '2013-12-23');

Вот так я начал делать, но какая-то хрень получается:

select terminate_date as y, count(*) from companies  
WHERE terminate_date is NOT null
group by terminate_date
order by Y

Я не нашел как расшарить код БД. Поэтому наверно можно заюзать для теста вот этот сервис https://sqliteonline.com/

Comments

[Константин Цветков]

Много разных.
SQL

Answer 1

[Константин Цветков]

SELECT CYear AS Date,
  (SELECT COUNT(*) FROM COMPANIES WHERE (YEAR(reg_date) = CY.CYear)) AS opened_count,
  (SELECT COUNT(*) FROM COMPANIES WHERE (YEAR(terminate_date) = CY.CYear)) AS terminate_count
  FROM (
    SELECT YEAR(C1.reg_date) AS CYear
      FROM COMPANIES AS C1
      WHERE (NOT (C1.reg_date IS NULL))
    UNION
    SELECT YEAR(C2.terminate_date) AS CYear
      FROM COMPANIES AS C2
      WHERE (NOT (C2.terminate_date IS NULL))) AS CY

Comments

[Максим]

Я прям боюсь спорить с метром, но этот код не оптимален (4 прохода по одной таблице).

[Константин Цветков]

Максим, При наличии индекса по датам всё уйдет на UNION. И поскольку запрос единичный для отчёта, нет смысла в его оптимизации.

[Максим]

Константин Цветков, У Вас YEAR(C1.reg_date) а это даже если по reg_date есть индекс, его не смогут использовать в полной мере (смогут но с обработкой). Надо создавать еще 2 индекса по YEAR(C1.reg_date) выделенному году, а это уже накладно. Да и условие (YEAR(reg_date) = CY.CYear)) говорит о том что надо делать индексы по выделенной части года. Запрос может быть отчетом (или частью отчета) и как следствие тормоза =(

Answer 2

[Максим]

Я не знаю с какой СУБД Вы работаете, но видел вы бросали ссылку на SQLite, написал в его синтаксисе хотя делаю это первый раз.

SELECT 
strftime('%Y', case Dub_date when 'Reg' then reg_date else terminate_date end) AS Date_,
count(case Dub_date when 'Reg' then reg_date end) AS opened_count,
count(case Dub_date when 'Terminate' then terminate_date end) As terminate_count
  FROM COMPANIES, (select 'Reg' Dub_date union all select 'Terminate')
group by Date_
having Date_ is not null

Comments

[beduin01]

Спасибо, в этот раз я с Oracle работал. У меня запрос в ответе выше получилось адаптировать. Но вам тоже спасибо.

[Максим]

Запрос в ответе выше проходит 4 раза по таблице, это беда. Для Oracle сейчас напишу, там помоему проще можно.