文章/答案/技术大牛

发布

社区首页 >问答首页 >如何合并多个case语句Postgresql

问如何合并多个case语句Postgresql
EN

Stack Overflow用户

提问于 2019-05-15 05:51:27

回答 1查看 85关注 0票数 0

编辑后的问题：

我有一张桌子

CREATE TABLE my_table (id INT, type_key int, rate_1 double precision, rate_2 double precision);

INSERT INTO my_table (id, type_key, rate_1, rate_2) 
VALUES
    (1, NULL, 0.2, 3),
    (2, 1, 1.3, 5),
    (3, 1, NULL, 10),
    (4, 2, 0.5, NULL),
    (5, 2, 0.01, 0),
    (6, 2, 0.75, NULL),
    (7, 3, NULL, NULL),
    (8, 3, 0.34, 1),
    (9, 3, NULL, 1);

理想情况下，结果表应该是：

(id, score_1, score_2)
    (1, NULL, NULL),
    (2, 0, 4),
    (3, NULL, 3),
    (4, 2, NULL),
    (5, 2, 4),
    (6, 3, NULL),
    (7, NULL, NULL),
    (8, 4, 2),
    (9, NULL, 3);

基于如下的评分阈值：

CASE WHEN type_key = 1 THEN (
            CASE
                WHEN rate_1 > .7 THEN 0
                WHEN rate_1 > .5 THEN 1
                WHEN rate_1 > .4 THEN 2
                WHEN rate_1 > .3 THEN 3
                ELSE 4

(对每个type_key_n和rate_n重复上述步骤)

原问题：

为了简单起见，我有一个表，假设它有三列：date、name和value。

我想在此基础上创建一个表，在此表中，我根据value的大小对每个名称进行了分类。

现在，这样做的一种方法是编写：

CREATE TABLE resulting_table AS 
(SELECT DATE,
       name,
       CASE
         WHEN value >= magnitude_1 THEN result_1
         WHEN value >= magnitude_2 THEN result_2
         WHEN value >= magnitude_n THEN result_n
       END AS bins
FROM my_table)

但是随着n的增加，这个查询会变得很长，而且很难辨认。如果我对不同的name有不同的阈值大小，那么我必须做一个嵌套的case语句，如下所示：

CREATE TABLE resulting_table AS 
(SELECT DATE,
       CASE
         WHEN name = 'name_n' THEN
           CASE
             WHEN value >= name_n_magnitude_n THEN result_n_n
           END AS bins 
FROM my_table)

这意味着查询会变得更长、更难读。

对于如何解决这个问题，我有两个想法，但我不太确定哪一个更好，或者我将如何实现它们。

1)为所有name、magnitude和result组合创建单独的表。将此表与my_table连接以获取resulting_table

2)混合使用postgresql/ python copg2和python，以一种易于阅读的方式实现此逻辑。

有什么想法吗？这似乎是一个常见的数据清理/数据工程问题。

python

postgresql

psycopg2

回答 1

Stack Overflow用户

发布于 2019-05-26 04:01:05

(第二个)已编辑问题的答案

假设和建模

假设每个type_key=n都存在一个rate_n，并通过数组而不是孤立的值对您的速率进行建模……因此，假设表中所有行都存在rate[type_key]。

CREATE TABLE my_table (
  id int,         -- same
  type_key int,   -- same
  rate double precision[] -- changed to array
);
INSERT INTO my_table 
    (id, type_key, rate) 
VALUES
    (1, NULL, array[0.2,  3, NULL]),
    (2, 1,    array[1.3,  5, NULL]),
    (3, 1,    array[NULL, 10, 0.2]),
    (4, 2,    array[0.5,  NULL,0.1]),
    (5, 2,    array[0.01, 0, 0.02]),
    (6, 2,    array[0.75, NULL,0.6]),
    (7, 3,    array[NULL::double precision, NULL,0.1]),
    (8, 3,    array[0.34, 1,0.31]),
    (9, 3,    array[NULL, 1,0.1])
;

补充样本

请提供更好的样本来测试解决方案...示例：

INSERT INTO my_table 
    (id, type_key, rate) 
VALUES
    (20, 1,    array[0.5, 0, 0]),
    (21, 1,    array[0.7, 0, 0]),
    (22, 2,    array[0, 0.7, 0])
;

解决方案

SELECT m.id, (
    SELECT COALESCE( max(idx)-1, 0 ) 
    FROM unnest(cmp[type_key:type_key]) WITH ORDINALITY tt(x,idx) 
    WHERE m.rate[type_key]>x
  ) score
FROM my_table m, 
     (select array[ 
       [0.0, 0.3,  0.4,  0.5,  0.7],  -- "case set" of rate_1
       [0.0, 0.31, 0.4,  0.45, 0.72], -- "case set" of rate_2
       [0.0, 0.22, 0.41, 0.55, 0.8]   -- "case set" of rate_3
      ]) t(cmp)
;

结果

 id | score 
----+-------
  1 |     0
  2 |     4
  3 |     0
  4 |     0
  5 |     0
  6 |     0
  7 |     0
  8 |     1
  9 |     0
 20 |     2
 21 |     3
 22 |     3

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/56139115

复制

相似问题

问如何合并多个case语句Postgresql
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何合并多个case语句PostgresqlEN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何合并多个case语句Postgresql
EN