Помогите с sql-запросом

Всё о СУБД PostgreSQL.

Модератор: zdn

Ответить
Newbee
Сообщения: 6
Зарегистрирован: 10.11.2022 15:30:17

Помогите с sql-запросом

Сообщение Newbee »

Всем привет!

Давно не брал SQL в руки, но все циклично.
Есть вот такая табличка:

Код: Выделить всё

CREATE TABLE test_tbl (
	u_name varchar NOT NULL,
	u_color varchar NULL
);

INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'YELLOW');
INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'YELLOW');
INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'GREEN');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'PERFECT');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'GREEN');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'GREEN');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'YELLOW');
INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'PERFECT');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'PERFECT');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'YELLOW');
INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'GREEN');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'GREEN');
INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'YELLOW');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'RED');
Нужен запрос, чтобы получить сумму цветов для каждой фамилии:
Name/Color|RED|YELLOW|GREEN|PURPLE
Ivanov | 3 | 1 | 2 | 0
Petrov | 2 | 3 | 1 | 1
Sidorov| 2 | 1 | 2 | 2
zdn
Сообщения: 11
Зарегистрирован: 02.04.2022 12:45:38

Re: Помогите с sql-запросом

Сообщение zdn »

Возможно так:

Код: Выделить всё

with
 RED as (select u_name, count(u_color) as color from test_tbl where u_color = 'RED' group by u_name),
 YELLOW as (select u_name, count(u_color) as color from test_tbl where u_color = 'YELLOW' group by u_name),
 GREEN as (select u_name, count(u_color) as color from test_tbl where u_color = 'GREEN' group by u_name),
 PURPLE as (select u_name, count(u_color) as color from test_tbl where u_color = 'PURPLE' group by u_name)
select RED.u_name, 
  coalesce(RED.color,0) as RED, 
  coalesce(YELLOW.color,0) as YELLOW, 
  coalesce(GREEN.color,0) as GREEN, 
  coalesce(PURPLE.color,0) as PURPLE
 from RED
  left join YELLOW on RED.u_name = YELLOW.u_name
  left join GREEN on YELLOW.u_name = GREEN.u_name
  left join PURPLE on GREEN.u_name = PURPLE.u_name
;
fraks
Сообщения: 26
Зарегистрирован: 06.04.2022 11:27:22

Re: Помогите с sql-запросом

Сообщение fraks »

В первичных данных нет цвета PURPLE, вместо него написан PERFECT.

Запрос может выглядеть так (синтаксис Firebird, для другого сервера может потребоваться заменить IIF на CASE или чего там еще есть):

Код: Выделить всё

select
  u_name,
  --
  sum( iif(u_color = 'RED'     , 1, 0) ) as RED,
  sum( iif(u_color = 'YELLOW'  , 1, 0) ) as YELLOW,
  sum( iif(u_color = 'GREEN'   , 1, 0) ) as GREEN,
  sum( iif(u_color = 'PERFECT' , 1, 0) ) as PERFECT,
  sum( iif(u_color = 'PURPLE'  , 1, 0) ) as PURPLE

from
  test_tbl

group by
  u_name
fraks
Сообщения: 26
Зарегистрирован: 06.04.2022 11:27:22

Re: Помогите с sql-запросом

Сообщение fraks »

Вот вариант для простгресса.

Код: Выделить всё

select
  u_name,
  --
  sum ( case when u_color = 'RED'     then 1 else 0 end ) as RED,
  sum ( case when u_color = 'YELLOW'  then 1 else 0 end ) as YELLOW,
  sum ( case when u_color = 'GREEN'   then 1 else 0 end ) as GREEN,
  sum ( case when u_color = 'PERFECT' then 1 else 0 end ) as PERFECT,
  sum ( case when u_color = 'PURPLE'  then 1 else 0 end ) as PURPLE

from
  test_tbl

group by
  u_name
;  

Код: Выделить всё

+=========+=====+========+=======+=========+========+
| u_name  | red | yellow | green | perfect | purple |
+=========+=====+========+=======+=========+========+
| Petrov  | 2   | 3      | 1     | 1       | 0      |
| Sidorov | 2   | 1      | 2     | 2       | 0      |
| Ivanov  | 3   | 1      | 2     | 0       | 0      |
+---------+-----+--------+-------+---------+--------+
Newbee
Сообщения: 6
Зарегистрирован: 10.11.2022 15:30:17

Re: Помогите с sql-запросом

Сообщение Newbee »

Земной поклон за помощь!
Newbee
Сообщения: 6
Зарегистрирован: 10.11.2022 15:30:17

Re: Помогите с sql-запросом

Сообщение Newbee »

А если предположить, что помимо Иванова, Петрова и Сидорова есть и другие фамилии, то можно их как-то объединить, например под "Остальные" и суммарно посчитать цвета и для них?
Конструкция "case when u_name in ('Ivanov', 'Petrov', 'Sidorov') then u_name else 'Other' end ) as u_name" явно не прокатит, так как все фамилии не из списка будут заменены на Other, но группировки и суммирования не произойдет.
Последний раз редактировалось Newbee 15.11.2022 15:32:57, всего редактировалось 2 раза.
fraks
Сообщения: 26
Зарегистрирован: 06.04.2022 11:27:22

Re: Помогите с sql-запросом

Сообщение fraks »

Newbee писал(а): 15.11.2022 15:10:44 Конструкция "case when u_name in ('Ivanov', 'Petrov', 'Sidorov') then u_name else 'Other' end ) as u_name" явно не прокатит, так как все фамилии не из списка будут заменены на Other, но группировки и суммирования не произойдет.
По какому полю сгруппируешь - по тому и будет. Группировать можно не только по полю но и по выражению.

Код: Выделить всё

select
  case when u_name in ('Ivanov', 'Petrov') then u_name else 'Other' end  as u_name,
  --
  sum ( case when u_color = 'RED'     then 1 else 0 end ) as RED,
  sum ( case when u_color = 'YELLOW'  then 1 else 0 end ) as YELLOW,
  sum ( case when u_color = 'GREEN'   then 1 else 0 end ) as GREEN,
  sum ( case when u_color = 'PERFECT' then 1 else 0 end ) as PERFECT,
  sum ( case when u_color = 'PURPLE'  then 1 else 0 end ) as PURPLE

from test_tbl
group by 1
;

Код: Выделить всё

+========+=====+========+=======+=========+========+
| u_name | red | yellow | green | perfect | purple |
+========+=====+========+=======+=========+========+
| Other  | 2   | 1      | 2     | 2       | 0      |
| Petrov | 2   | 3      | 1     | 1       | 0      |
| Ivanov | 3   | 1      | 2     | 0       | 0      |
+--------+-----+--------+-------+---------+--------+
Newbee
Сообщения: 6
Зарегистрирован: 10.11.2022 15:30:17

Re: Помогите с sql-запросом

Сообщение Newbee »

Спасибо! Забыл что можно группировать по порядковому номеру, а не названию поля.
Newbee
Сообщения: 6
Зарегистрирован: 10.11.2022 15:30:17

Re: Помогите с sql-запросом

Сообщение Newbee »

Всем доброго времени суток!

Продолжаю быть начинающим в PostgreSQL. Случилась крайне странная ситуация - не срабатывает команда update.
Для теста создал схему, в ней две таблицы - tbl и tbl_h, на обновление tbl повесил триггер - он перекидывает предыдущие значения в таблицу tbl_h (историческая). Запускаю update и commit (на всякий случай) - update "проходит", но в основной таблице ничего не меняется, а триггер срабатывает и добавляет строку в таблицу с историей.
Пытаюсь разобраться в чем проблема. Подскажите новичку куда копать.

Код: Выделить всё

DROP SCHEMA IF EXISTS test CASCADE;
DROP ROLE IF EXISTS test;
commit;

CREATE USER test WITH 
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    LOGIN
    NOREPLICATION
    NOBYPASSRLS
    CONNECTION LIMIT -1
    PASSWORD 'qwerty';
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION test;

CREATE TABLE test.tbl (
    tbl_id integer NOT NULL,
    tbl_name varchar(255) NOT NULL,
    CONSTRAINT tbl_pk PRIMARY KEY (tbl_id)
) WITH (
  OIDS=FALSE
);

CREATE TABLE test.tbl_h (
    tbl_id integer NOT NULL,
    tbl_name varchar(255) NOT NULL,
    tbl_h_dt TIMESTAMP
) WITH (
  OIDS=FALSE
);

CREATE OR REPLACE FUNCTION tbl_h_fnc ()
RETURNS trigger AS
$$
DECLARE

BEGIN
insert into test.tbl_h select t.tbl_id, t.tbl_name, current_timestamp from test.tbl t;
RETURN NULL;
END;

$$ LANGUAGE 'plpgsql';

CREATE TRIGGER tbl_h_trg
     BEFORE UPDATE ON test.tbl
     FOR EACH ROW EXECUTE PROCEDURE tbl_h_fnc();
     

ALTER TABLE test.tbl_h ADD CONSTRAINT tbl_h_fk FOREIGN KEY (tbl_id) REFERENCES test.tbl(tbl_id);


select * from test.tbl t;
select * from test.tbl_h th;

insert into test.tbl values (111, 'Test');

update test.tbl set tbl_name = 'BLABLABLA' where tbl_id = 111;
commit;
select * from test.tbl t;
select * from test.tbl_h th;
P.S. Все тесты гоняю в DBeaver 22.2.4.
Newbee
Сообщения: 6
Зарегистрирован: 10.11.2022 15:30:17

Re: Помогите с sql-запросом

Сообщение Newbee »

Нашел сам.
В функции надо возвращать значение NEW, а не NULL.
Ответить