在PostgreSQL中插入具有最大行的元素。

huangapple go评论108阅读模式
英文:

Insert elements postgresql with maximum row

问题

我正在尝试创建一个与Postgres一起使用的插入脚本,

将最多插入每个人的一个元素,

例如,我有这个查询,但我想创建一个逻辑,如果我已经向一个人插入了一个文件,下一个文件应该插入到该国家的下一个人

我对这种情况感到困惑,以前从未见过这样的东西,一个只插入一行然后插入下一个“人”的脚本。

现在发生的情况是我向一个人插入3个文件,而同一国家的其他人没有收到文件。

WITH get_files AS (
    SELECT
        type,
        country
    FROM files
    WHERE title IS NOT NULL
)
INSERT INTO history_person_file (
    name,
    document_type,
    age
)
SELECT
    p.name,
    f.type,
    p.age
FROM person p
LEFT JOIN get_files f
ON p.country = f.country
ON conflict (name) do nothing;
英文:

I am trying to create a insert script with postgres,

To insert into a table a maximum 1 element per person,

For example, I got this query down, but I would like to create a logic that if I already inserted a file to a person, the next file should go to the next person of that country

I'm so stucked with this situation, I've never seen something like this before, a script that insert just one row and then inserts to the next "person".

What is happening now is that I'm inserting 3 files to a person, and the other person from the same country doenst receive a file.

WITH get_files AS (
	SELECT
		type,
		country
	FROM files
	WHERE title IS NOT NULL
)
INSERT INTO history_person_file (
	name,
	document_type,
	age
)
SELECT
	p.name,
	f.type,
	p.age
FROM person p
LEFT JOIN get_files f
ON p.country = f.country
ON conflict (name) do nothing;

答案1

得分: 2

在查询中执行限制逻辑。一旦你做对了,将结果插入到你的历史表中:

with num_docs as (
 select type, country, 
        row_number() over (partition by country 
                               order by title) as rn
   from files
  where title is not null
), num_person as (
 select name, age, country,
        row_number() over (partition by country
                               order by name) as rn
   from person
)
select p.country, p.name, d.country, d.type, d.age
  from num_docs d
       full join num_person p 
         on (p.country, p.rn) = (d.country, d.rn);

我在这里使用了 full join,这样你可以在加载到历史表之前查看结果。

英文:

Perform the restriction logic in your query. Once you have that right, insert the result into your history table:

with num_docs as (
 select type, country, 
        row_number() over (partition by country 
                               order by title) as rn
   from files
  where title is not null
), num_person as (
 select name, age, country,
        row_number() over (partition by country
                               order by name) as rn
   from person
)
select p.country, p.name, d.country, d.type, d.age
  from num_docs d
       full join num_person p 
         on (p.country, p.rn) = (d.country, d.rn);

I used a full join here so you can review the results before loading to your history table.

答案2

得分: 1

不确定但也许这会起作用

    WITH RankedPersons AS (
    SELECT
        p.name,
        p.age,
        p.country,
        ROW_NUMBER() OVER (PARTITION BY p.country ORDER BY f.title IS NULL) AS 
    row_num
    FROM person p
    LEFT JOIN files f ON p.country = f.country AND f.title IS NOT NULL
    ),
    NextAvailableFile AS (
    SELECT
        f.type,
        f.country
    FROM files f
    LEFT JOIN RankedPersons p ON f.country = p.country AND p.row_num = 1
    WHERE p.row_num IS NULL
    LIMIT 1
    )
    INSERT INTO person_file (name, document_type, age)
    SELECT
    p.name,
    COALESCE(naf.type, 'default_document_type'),
    p.age
    FROM RankedPersons p
    LEFT JOIN NextAvailableFile naf ON p.country = naf.country
    WHERE p.row_num = 1
    ON CONFLICT (name) DO NOTHING;
英文:

Not sure but maybe this will work

WITH RankedPersons AS (
SELECT
    p.name,
    p.age,
    p.country,
    ROW_NUMBER() OVER (PARTITION BY p.country ORDER BY f.title IS NULL) AS 
row_num
FROM person p
LEFT JOIN files f ON p.country = f.country AND f.title IS NOT NULL
),
NextAvailableFile AS (
SELECT
    f.type,
    f.country
FROM files f
LEFT JOIN RankedPersons p ON f.country = p.country AND p.row_num = 1
WHERE p.row_num IS NULL
LIMIT 1
)
INSERT INTO person_file (name, document_type, age)
SELECT
p.name,
COALESCE(naf.type, 'default_document_type'),
p.age
FROM RankedPersons p
LEFT JOIN NextAvailableFile naf ON p.country = naf.country
WHERE p.row_num = 1
ON CONFLICT (name) DO NOTHING;

huangapple
  • 本文由 发表于 2023年8月11日 02:57:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/76878634.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定