Запрос в postgresql

Question

Имеются таблицы:

types: id, title-содержит большое количество расширений(png, bmp, ... итд]

files: id, idFolder, name, typeId

Folders: id, folderName

Пытаюсь запросом или запросами получить такую таблицу:

result: folder.id, folderName, sum(types)-выводится количество расширений имеющихся в файле, typeFiles1, typeFiles2, ... - выводятся столбцы с названием расширений и в них значения равняющиеся количеству файлов данного типа в папке

Использую postgreSQL, запускаю в pgAdmin в браузере

id	title
1	png
2	bmp
...	...

id	folderName
1	Folder1
2	Folder2
...	...

id	idFolder	typeId	name
1	1	1	picture1
2	1	1	picture2
3	1	2	picture3
4	1	2	picture4
5	1	2	picture5
6	2	1	picture6
7	2	1	picture7

Итоговый результат:

folder.id	folder.title	К-во файлов в папке	png	bmp	...
1	Folder1	5	2	3	...
2	Folder2	2	2	0	...

UDP: На данный момент получил такую таблицу:

idFolder	folderName	type	Количество
1	Folder1	png	2
1	Folder1	bmp	5
2	Folder2	png	2

Как далее вынести типы в столбцы не знаю, crosstab не получается

CREATE TABLE public.files
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9999999 CACHE 1 ),
    folder_id integer,
    type_id integer,
    title character varying COLLATE pg_catalog."default"
)

CREATE TABLE public.folders
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 999999 CACHE 1 ),
    title character varying COLLATE pg_catalog."default",
    CONSTRAINT folders_pkey PRIMARY KEY (id)
)

CREATE TABLE public.types
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9999999 CACHE 1 ),
    title character varying COLLATE pg_catalog."default",
    CONSTRAINT types_pkey PRIMARY KEY (id)
)

insert into files (folder_id, type_id, title
values (1, 1, 'picture1')
insert into files (folder_id, type_id, title)
values (1, 1, 'picture2')
insert into files (folder_id, type_id, title)
values (1, 2, 'picture3')
insert into files (folder_id, type_id, title
values (1, 2, 'picture4')
insert into files (folder_id, type_id, title)
values (1, 2, 'picture5')
insert into files (folder_id, type_id, title)
values (2, 1, 'picture6')
insert into files (folder_id, type_id, title)
values (2, 1, 'picture7')

insert into folders (title)
values ('folder1')
insert into folders (title)
values ('folder2')


insert into types (title)
values ('png')
insert into types (title)
values ('bmp')

Answer 1

SELECT DISTINCT
       folders.id folder_id,
       folders.title folder_title,
       COUNT(*) OVER (PARTITION BY folders.id) total_count,
       SUM((types.title = 'bmp') :: INT) OVER (PARTITION BY folders.id) bmp,
       SUM((types.title = 'png') :: INT) OVER (PARTITION BY folders.id) png
FROM files
JOIN folders ON files.folder_id = folders.id
JOIN types ON files.type_id = types.id
ORDER BY 1;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1ad7e0d5fbb7451577223618a8bbaa8a

БЛОГ НА HUSL

Запрос в postgresql

Ответы (1 шт):