sql - 关于连接表和动态类型后如何计算的SQL问题

Question

在此处查看和测试表和我的查询：http: //sqlfiddle.com/# !17/e5a87/3

我对 SQL 很陌生，我得到了这 3 个表：

tb1 是存储级别的数据

create table tb1 (id varchar(1), store_id varchar(3), sold_count int);
insert into tb1 values
('1', 's1', 40),
('2', 's2', 20),
('3', 's2', 30);

tb2 是关于每个产品的数据

create table tb2 (id varchar(1), product_id varchar(3), shelf_id varchar(4), error_type varchar(24));
insert into tb2 values
('a', 'p1', 'row1', 'A'),
('b', 'p2', 'row2', 'A'),
('c', 'p3', 'row3', 'B'),
('d', 'p4', 'row4', 'C'),
('e', 'p5', 'row4', 'C');

tb3是一个连接store和product的表，一个store可能有多个product

create table tb3 (tb1_id varchar(1), tb2_id varchar(1));
insert into tb3 values
('1', 'a'),
('2', 'b'),
('2', 'c'),
('3', 'd'),
('3', 'e');

现在我想写一个查询并得到这样的结果：

store_id	total_sold	总货架	百分比	type_A	type_B	type_C
s1	40	1	0.025 (1/40)	1	0	0
s2	50 (20+30)	3	0.06 (3/50)	1	1	2

我写了一个查询如下：

SELECT
  tb1.store_id,
  SUM(tb1.sold_count) AS total_sold,
  MAX(t.shelves_count) AS total_shelf_count,
  (MAX(t.shelves_count) / SUM(tb1.sold_count)) AS percentage, 
  t.A,
  t.B,
  t.C
FROM tb1
  JOIN (
    SELECT
      tb1.store_id AS store_id,
      COUNT(DISTINCT tb2.shelf_id) AS shelves_count,
      SUM(CASE WHEN tb2.error_type = 'A' THEN 1 ELSE 0 END) AS A,
      SUM(CASE WHEN tb2.error_type = 'B' THEN 1 ELSE 0 END) AS B,
      SUM(CASE WHEN tb2.error_type = 'C' THEN 1 ELSE 0 END) AS C
      FROM tb1
        JOIN tb3 ON tb3.tb1_id = tb1.id
        JOIN tb2 ON tb3.tb2_id = tb2.id
      GROUP BY store_id
  ) AS t ON tb1.store_id = t.store_id
GROUP BY tb1.store_id, t.A, t.B, t.C;

它做的大多数事情都是正确的，但我有两个问题：

（1）百分比列似乎不起作用，它总是给我0，当它应该有一个数字时，我该如何解决？

（2）如果我有很多错误类型（不仅仅是A、B、C），我真的不能把它们都具体列出来。有没有办法让 postgresql 自动收集不同的类型和计数，并且仍然显示这样的最终结果？

真的需要一些帮助，提前谢谢！！请在此处查看示例表和我当前的查询：http: //sqlfiddle.com/# !17/e5a87/3

标签： sqlpostgresql

Answer 1

您需要在对值进行除法之前转换类型，否则PostgreSQL将在您执行聚合函数时使用 bigint 进行计算。

查询 1：

SELECT
  tb1.store_id,
  SUM(tb1.sold_count) AS total_sold,
  MAX(t.shelves_count) AS total_shelf_count,
  (MAX(t.shelves_count)::decimal / SUM(tb1.sold_count)) AS percentage, 
  t.A,
  t.B,
  t.C
FROM tb1
  JOIN (
    SELECT
      tb1.store_id AS store_id,
      COUNT(DISTINCT tb2.shelf_id) AS shelves_count,
      SUM(CASE WHEN tb2.error_type = 'A' THEN 1 ELSE 0 END) AS A,
      SUM(CASE WHEN tb2.error_type = 'B' THEN 1 ELSE 0 END) AS B,
      SUM(CASE WHEN tb2.error_type = 'C' THEN 1 ELSE 0 END) AS C
      FROM tb1
        JOIN tb3 ON tb3.tb1_id = tb1.id
        JOIN tb2 ON tb3.tb2_id = tb2.id
      GROUP BY store_id
  ) AS t ON tb1.store_id = t.store_id
GROUP BY tb1.store_id, t.A, t.B, t.C

结果：

| store_id | total_sold | total_shelf_count | percentage | a | b | c |
|----------|------------|-------------------|------------|---|---|---|
|       s2 |         50 |                 3 |       0.06 | 1 | 1 | 2 |
|       s1 |         40 |                 1 |      0.025 | 1 | 0 | 0 |

您可以尝试使用pg_typeof来查看值的类型。

查询 1：

SELECT
  pg_typeof(SUM(tb1.sold_count)) AS total_sold,
  pg_typeof(MAX(t.shelves_count)) AS total_shelf_count,
  pg_typeof(MAX(t.shelves_count)::decimal / SUM(tb1.sold_count)) AS total_shelf_count
FROM tb1
  JOIN (
    SELECT
      tb1.store_id AS store_id,
      COUNT(DISTINCT tb2.shelf_id) AS shelves_count,
      SUM(CASE WHEN tb2.error_type = 'A' THEN 1 ELSE 0 END) AS A,
      SUM(CASE WHEN tb2.error_type = 'B' THEN 1 ELSE 0 END) AS B,
      SUM(CASE WHEN tb2.error_type = 'C' THEN 1 ELSE 0 END) AS C
      FROM tb1
        JOIN tb3 ON tb3.tb1_id = tb1.id
        JOIN tb2 ON tb3.tb2_id = tb2.id
      GROUP BY store_id
  ) AS t ON tb1.store_id = t.store_id

[结果]：

| total_sold | total_shelf_count | total_shelf_count |
|------------|-------------------|-------------------|
|     bigint |            bigint |           numeric |