Как правильно записать SQL запрос?

Question

[dflbrhekbn]

Есть две таблицы : db_person со столбцами id, stat
db_person_fio cо столбцами id_person, dr(дата рождения), w(1- мужчина, 2 - женщина)
Нужно составить запрос который будет показывать сколько есть в базе человек для каждого w (мужчина , женщина) с интервалом в 10 лет, и stat > 0 .Например :
Мужчин Женщин
0-10 1000 548
11-21 4564 8944
и так далее

Comments

[idShura]

Ну как разобрался?

Answer 1

[idShura]

MySQL:

SELECT T.YEAR,
         SUM (CASE WHEN T.W = 1 THEN 1 ELSE 0 END) AS MEN,
         SUM (CASE WHEN T.W = 2 THEN 1 ELSE 0 END) AS WOMEN
    FROM (SELECT (YEAR(CURRENT_DATE) - YEAR(F.DR)) DIV 10 * 10 AS YEAR, F.W
            FROM DB_PERSON AS P LEFT JOIN DB_PERSON_FIO AS F ON P.ID = F.ID_PERSON
           WHERE P.STAT > 0) AS T
   
GROUP BY T.YEAR
ORDER BY T.YEAR;

Шаг в 10 лет:

1) YEAR(CURRENT_DATE) - YEAR(F.DR) - из текущей даты вычитаем дату рождения чтобы получить количество лет. (не учитывая месяцы и дни по этому такой способ не очень точный)
2) DIV 10 * 10 - делим без остатка на 10 и затем умножаем снова на 10 чтобы получить шаг в 10 лет.

Немного изменил:

SELECT T.YEAR,
         SUM (CASE WHEN T.W = 1 THEN 1 ELSE 0 END) AS MEN,
         SUM (CASE WHEN T.W = 2 THEN 1 ELSE 0 END) AS WOMEN
    FROM (SELECT (DATE_FORMAT(FROM_DAYS(TO_DAYS(CURRENT_DATE) - TO_DAYS(F.DR)), '%Y') + 0) DIV 10 * 10 AS YEAR, 
                 F.W
            FROM DB_PERSON AS P LEFT JOIN DB_PERSON_FIO AS F ON P.ID = F.ID_PERSON
           WHERE P.STAT > 0) AS T
GROUP BY T.YEAR
ORDER BY T.YEAR;

Comments

[dflbrhekbn]

А может сможете подсказать. В mysql workbench при запуске запроса появляется ошибка : Error Code: 1248. Every derived table must have its own alias
Как её исправить ?

[idShura]

dflbrhekbn: изменил код, добавил "AS TAB". Я писал запрос под Oracle, но если могу помочь написать под MySql если нужно.

[dflbrhekbn]

будьте любезны если не сложно. Особенно интересно как задать этот шаг в 10 лет . Не могу понять разобрать. Мой workbench ругается на TRUNC

[V Sh.]

сам по себе пример кода сомнителен. Объясните зачем вы проводите непонятные манипуляции с sysdate и dr? У вас там ошибка приведения типов (деление даты на число), да и без этой ошибки непонятен ход ваших мыслей. Если вы хотели вычислить десятилетие, то нужно делать как-то так: floor(months_between(trunc(sysdate),trunc(db_person_fio.dr))/12). При вычитании дат Oracle вернет либо разницу дней между датами. Да и даже если учесть, что все там верно (что не так), то в вашем коде не все возраста будут представлены. Если, например, категория 10-20 не будет представлена никем, то вместо строки "10-20 0 0", строки вообще не будет.

[dflbrhekbn]

idShura: результат выполнения запроса
YEAR MEN WOMEN
0 1233 5445

и ПОДСКАЖИТЕ что как так получается что выражение SELECT T.YEAR выводит не существующий столбец ?

[idShura]

dflbrhekbn: T.YEAR вычислялся ранее в подзапросе (Алиас AS YEAR)

Answer 2

[V Sh.]

Синтаксис MySQL.
Вот структура таблиц и запрос. Еще нужно создать дополнительную таблицу для возрастов.

CREATE TABLE db_person
(
id integer,
stat integer
);

CREATE TABLE db_person_fio
(
id_person integer,
dr date,
w integer
);

CREATE TABLE db_person_ages
(
age integer
);

INSERT INTO db_person_ages(age) VALUES (0);
INSERT INTO db_person_ages(age) VALUES (1);
INSERT INTO db_person_ages(age) VALUES (2);
INSERT INTO db_person_ages(age) VALUES (3);
INSERT INTO db_person_ages(age) VALUES (4);
INSERT INTO db_person_ages(age) VALUES (5);
INSERT INTO db_person_ages(age) VALUES (6);
INSERT INTO db_person_ages(age) VALUES (7);
INSERT INTO db_person_ages(age) VALUES (8);
INSERT INTO db_person_ages(age) VALUES (9);
INSERT INTO db_person_ages(age) VALUES (10);

SELECT CONCAT(age*11,'-',age*11+10) as descr,
	   IFNULL(man,0) as man,
       IFNULL(woman,0) as woman
FROM db_person_ages ages
LEFT JOIN 
(
SELECT FLOOR(timestampdiff(YEAR,f.dr,CURRENT_DATE)/11) as diff,
	   SUM(CASE WHEN f.w=1 THEN 1 ELSE 0 END) as man,
       SUM(CASE WHEN f.w=0 THEN 1 ELSE 0 END) as woman
FROM db_person p, db_person_fio f
WHERE p.id = f.id_person
AND p.stat>0
GROUP BY 1
) t ON ages.age = t.diff

Comments

[dflbrhekbn]

ОГО. Целый том. А нельзя ли как то задать к примеру так : что бы машина сама взяла 2016 год отняла от него 10 лет и посчитала сколько женщин и мужчин в этот промежуток. Затем 2006 год - 10 лет и сколько в этот промежуток есть мужчин и женщин. Ия делаю все это не в оракле , а в Workbench - я так понимаю между ними есть различия !

[V Sh.]

dflbrhekbn: Я не уверен, что в MySQL есть конструкция CONNECT BY, да и (+) необходимо будет поменять на общепринятый LEFT JOIN. Давайте, когда приду домой (часа через два) и если не будет еще удобного для вас решения, то я найду online редактор MySQL и напишу решение, которое вам нужно будет просто скопировать и вставить.
По сути, все, что требуется, это:
1) вычислить для каждого человека к какому десятилетию он относится. У меня это делается вот так: TRUNC(floor(months_between(trunc(sysdate),trunc(db_person_fio.dr))/12)/10). Берем разницу в месяцах между текущей датой (sysdate) и днем рождения и делим все на 120 (12 месяцев*10 лет).
2) группируем получившиеся строки по этому признаку и находим суммы для групп (мужчины, женщины). Если строчки типа не нужны "0-10 0 0", когда нет записей удовлетворяющих этому условию, то можно прекратить.
3) Если же эти строчки важны, то нужно сделать пересечение с таблицей по десятилетиям.

[dflbrhekbn]

хорошо. Напишите пожалуйста как сможете. А там может я с вашей подсказкой сам смогу догадаться !

[V Sh.]

dflbrhekbn: Изменил ответ. В MySQL нет CONNECT BY PRIOR (рекурсивный вызов) поэтому вместо него создал дополнительную табличку. Можно и без таблички (внутренний запрос), но тогда информация будет только по тому, что есть в базе.

[dflbrhekbn]

Увы не могу проверить =( У меня нет прав на создание новых и редактирование старых таблиц (база данных фирмы). Но вот перепал запрос который подходит, но полностью расшифровать что в нем происходит не могу. Не могли мне балбесу распить ?

select w,
sum(if(gr between 0 and 9, kol, 0)) as gr010,
sum(if(gr between 10 and 19, kol, 0)) as gr1019,
sum(if(gr between 20 and 29, kol, 0)) as gr2029,
sum(if(gr between 30 and 39, kol, 0)) as gr3039
from(
select f.w, count(f.w) as kol, year(curdate())-year(f.dr) as gr from db_person_fio f where f.w in (1,2) group by f.w, 3
)lev

group by 1

Особенно интересно if(gr between 0 and 9, kol, 0) и SELECT который находится в (FROM)