Postgresql request



  • Tables are available:

    types: id, title-contains a large number of extensions (png, bmp, ... id)

    files: id, idFolder, name, typeId

    Folders: id, folderName

    Trying to obtain such a table as is requested or requested:

    result: folder.id, folderName, sum(types) - the number of extensions available in the file, typeFiles1, typeFiles2, ... - Exhaust columns with the name of extensions and the values of the same number of files in the folder

    Using postgreSQL, starting at pgAdmin in the browser

    idtitle
    1png
    2bmp
    ♪ ♪ ♪♪ ♪ ♪
    idfolderName
    1Folder1
    2Folder2
    ♪ ♪ ♪♪ ♪ ♪
    ididFoldertypeIdname
    111picture1
    211picture2
    312picture3
    412picture4
    512picture5
    621picture6
    721picture7

    Outcome:

    folder.idfolder.K-y-your files in the file.pngbmp♪ ♪ ♪
    1Folder1523♪ ♪ ♪
    2Folder2220♪ ♪ ♪

    UDP: To date, it has received such a table:

    idFolderfolderNametypeNumber
    1Folder1png2
    1Folder1bmp5
    2Folder2png2

    I don't know how to put the type in the columns, crosstab doesn't work.

    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')



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2