PostgreSQL select self referencing with two columns，part 2

50few1ms 于 2023-04-05 发布在 PostgreSQL

关注(0)|答案(2)|浏览(86)

基于最初的问题，我需要计数器PostgreSQL select self referencing with two columns
除了柜台我需要从客户端和咨询本身的数据，这里是更好的问题，我需要什么
我有客户，亲戚（定义客户之间的关系及其类型）和咨询。我不想做的是为合作伙伴提供新类型的咨询，并在咨询时计算他们的年龄。问题是，可能会发生两个客户都有一个类型为“New”的咨询，但我只想计数一个，因为我我只对我有多少合作伙伴参与咨询感兴趣。
下面是我的数据库的简化SQL

CREATE TABLE clients (
  id INT NOT NULL,
  name VARCHAR NOT NULL,
  birthday DATE NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE relations (
  id INT NOT NULL,
  from_client_id INT NOT NULL,
  to_client_id INT NOT NULL,
  relation_type VARCHAR NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE consultations (
  id INT NOT NULL,
  client_id INT NOT NULL,
  type_of VARCHAR NOT NULL,
  "date" timestamp without time zone,
  PRIMARY KEY(id)
);

一些种子数据
x一个一个一个一个x一个一个二个一个x一个一个三个一个
选择数据给了我三个咨询：

SELECT 
CASE 
  WHEN date_part('year', AGE(consultations.date, clients.birthday)) BETWEEN 0 AND 17 THEN '0 - 18'
  WHEN date_part('year', AGE(consultations.date, clients.birthday)) BETWEEN 18 AND 25 THEN '18-25'
  WHEN date_part('year', AGE(consultations.date, clients.birthday)) BETWEEN 26 AND 40 THEN '26-40'
  WHEN date_part('year', AGE(consultations.date, clients.birthday)) BETWEEN 41 AND 60 THEN '41-60'
  WHEN date_part('year', AGE(consultations.date, clients.birthday)) BETWEEN 60 AND 200 THEN '60+'
END AS "Age",
count(*) AS "Count"
FROM consultations
INNER JOIN clients ON consultations.client_id = clients.id 
  AND ("consultations"."date" BETWEEN '2022-01-01 00:00' AND '2022-12-31 23:59')
  AND "consultations"."type_of" = 'New'
INNER JOIN relations ON (relations.from_client_id = consultations.client_id OR relations.to_client_id = consultations.client_id)
  AND relations.relation_type = 'partner'
GROUP BY "Age"
ORDER BY "Age";

这是我得到的：

Age Count
26-40   1
41-60   2

但我真正想要的是

Age Count
26-40   1
41-60   1

这是因为作为“合作伙伴”的John Doe和Jane Doe都有一个“新”类型的咨询，但在这种情况下，我只想计数一个咨询，因为我只对咨询中有多少合作伙伴感兴趣，这个选择将我的Jane和John都计数为合作伙伴。
所以我对John Doe和Jane Doe的预期结果是1，对Fiddle Blaab和Agnes Blaab的预期结果是第二个。
我创建了一个db fiddle
这让我抓狂，我知道我可以编写代码，但我想用sql来做，这样比在代码中循环所有这些记录要快得多。

postgresql

来源：https://stackoverflow.com/questions/75820740/postgresql-select-self-referencing-with-two-columns-part-2

2条答案

按热度按时间

5gfr0r5j1#

虽然不确定它是否有效，但这个查询将完成这项工作：

select 
  min(CASE 
  WHEN date_part('year', AGE(c.date, clients.birthday)) BETWEEN 0 AND 17 THEN '0 - 18'
  WHEN date_part('year', AGE(c.date, clients.birthday)) BETWEEN 18 AND 25 THEN '18-25'
  WHEN date_part('year', AGE(c.date, clients.birthday)) BETWEEN 26 AND 40 THEN '26-40'
  WHEN date_part('year', AGE(c.date, clients.birthday)) BETWEEN 41 AND 60 THEN '41-60'
  WHEN date_part('year', AGE(c.date, clients.birthday)) BETWEEN 60 AND 200 THEN '60+'
END) AS "Age",count(distinct r.id)
  from
(select id,from_client_id client_id from relations where relation_type='partner'
  union all
select id,to_client_id client_id from relations where relation_type='partner')r
inner join consultations c on r.client_id=c.client_id and c.type_of = 'New'
inner join clients on r.client_id=clients.id
group by r.id

年龄	计数
41岁至60岁	1
二十六至四十岁	1

fiddle

赞(0）回复(0）举报 2023-04-05

hmtdttj42#

try this..> 

select 
  summary.age, 
  count(distinct summary.age) as count_age > 
from 
  (
    select 
      allr.client_id >, 
      CASE > WHEN date_part(
        'year', 
        AGE(con.date, cli.birthday)
      ) BETWEEN 0 
      AND 17 THEN '0-18' > WHEN date_part(
        'year', 
        AGE(con.date, cli.birthday)
      ) BETWEEN 18 
      AND 25 THEN '18-25' > WHEN date_part(
        'year', 
        AGE(con.date, cli.birthday)
      ) BETWEEN 26 
      AND 40 THEN '26-40' > WHEN date_part(
        'year', 
        AGE(con.date, cli.birthday)
      ) BETWEEN 41 
      AND 60 THEN '41-60' > WHEN date_part(
        'year', 
        AGE(con.date, cli.birthday)
      ) BETWEEN 60 
      AND 200 THEN '60+' > END AS age > --
      > 
    from 
      (
        select 
          r1.from_client_id as client_id 
        from 
          relations r1 
        where 
          r1.relation_type = 'partner' > 
        union 
          > 
        select 
          r2.to_client_id as client_id 
        from 
          relations r2 
        where 
          r2.relation_type = 'partner' >
      ) allr > --
      > 
      inner join consultations con on (
        allr.client_id = con.client_id 
        and con.type_of = 'New'
      ) > 
      inner join clients cli on (allr.client_id = cli.id) >
  ) summary 
group by 
  summary.age

赞(0）回复(0）举报 2023-04-05

我来回答

PostgreSQL select self referencing with two columns，part 2

2条答案

相关问题

热门标签

最新问答