sql - postgres中太多案例的替代方案

Question

我的要求是根据 itemId 值我需要在 Postgres 查询中选择一个值。所以我正在做这样的事情:

select itemId,
 CASE
-- Revenue
    WHEN itemId in (339,28,29)
    THEN 'Revenues'
    -- Rev CHG calculated on the back-end
--Expenditures
    WHEN itemId = 102
    THEN 'SG'||CHR(38)||'A'
    -- SGA CHG calculated on the back-end
    WHEN itemId = 4375
    THEN 'SG'||CHR(38)||'A MARGIN'
    WHEN itemId = 100
    THEN 'R'||CHR(38)||'D EXPENSE'
    -- RD CHG calculated on the back-end
    WHEN itemId  in (2021, 2114)
    THEN 'CAPEX'
    --CAPEX CHG calculated on the back-end
    WHEN itemId = 34
    THEN 'COGS'
--Income
    WHEN itemId = 379
    THEN 'Net Income'
    WHEN itemId = 7
    THEN 'Earnings from Cont. Ops'
    WHEN itemId = 83
    THEN 'Minority Interest'
    WHEN itemId = 280
    THEN 'Preferred Stock Dividend'
    -- NI CHG calculated on the back-end
    WHEN itemId = 400
    THEN 'EBIT'
    WHEN itemId = 4197
    THEN 'EBIT CHG 1 YR'
    WHEN itemId = 4210
    THEN 'EBIT CHG 2 YR'
    WHEN itemId = 4223
    THEN 'EBIT CHG 3 YR'
    WHEN itemId = 4236
    THEN 'EBIT CHG 5 YR'
    WHEN itemId = 4249
    THEN 'EBIT CHG 7 YR'
    WHEN itemId = 4262
    THEN 'EBIT CHG 10 YR'
    WHEN itemId = 4051
    THEN 'EBITDA'
    WHEN itemId = 4196
    THEN 'EBITDA CHG 1 YR'
    WHEN itemId = 4209
    THEN 'EBITDA CHG 2 YR'
    WHEN itemId = 4222
    THEN 'EBITDA CHG 3 YR'
    WHEN itemId = 4235
    THEN 'EBITDA CHG 5 YR'
    WHEN itemId = 4248
    THEN 'EBITDA CHG 7 YR'
    WHEN itemId = 4261
    THEN 'EBITDA CHG 10 YR'
    WHEN itemId = 4047
    THEN 'EBITDA MARGIN'
    WHEN itemId = 3064
    THEN 'EPS-BASIC'
    --EPS-BASIC CHG calculated on the back-end
    WHEN itemId = 142
    THEN 'EPS-DIL'
    --EPS-DIL CHG calculated on the back-end
    WHEN itemId = 10
    THEN 'GROSS PROFIT'
    WHEN itemId = 4195
    THEN 'GP CHG 1 YR'
    WHEN itemId = 4208
    THEN 'GP CHG 2 YR'
    WHEN itemId = 4221
    THEN 'GP CHG 3 YR'
    WHEN itemId = 4234
    THEN 'GP CHG 5 YR'
    WHEN itemId = 4247
    THEN 'GP CHG 7 YR'
    WHEN itemId = 4260
    THEN 'GP CHG 10 YR'
    WHEN itemId = 4074
    THEN 'GP MARGIN'
    --GP MARGIN CHG calculated on the back-end
    WHEN itemId in (21,5,356,22)
    THEN 'OP PROFIT'
    WHEN itemId = 373
    THEN 'Total Operating Exp'
    --OP PROFIT CHG calculated on the back-end
--Cash Flow
    WHEN itemId = 2207
    THEN 'Cash Flow'
    --CF CHG calculated on the back-end
    WHEN itemId = 4423
    THEN 'UNLEVERED FREE CASH FLOW'
    WHEN itemId = 4430
    THEN 'UCF CHG 1 YR'
    WHEN itemId = 4431
    THEN 'UCF CHG 2 YR'
    WHEN itemId = 4432
    THEN 'UCF CHG 3 YR'
    WHEN itemId = 4433
    THEN 'UCF CHG 5 YR'
    WHEN itemId = 4434
    THEN 'UCF CHG 7 YR'
    WHEN itemId = 4435
    THEN 'UCF CHG 10 YR'
    WHEN itemId = 2006
    THEN 'CASH OPS'
    WHEN itemId = 2081
    THEN 'Net Cash From Discont. Ops'
    --Cash Ops CHG calculated on the back-end
--Return
    WHEN itemId = 4178
    THEN 'ROA'
    WHEN itemId = 4363
    THEN 'ROC'
    WHEN itemId = 4128
    THEN 'ROE'
--Working Capital
    WHEN itemId = 4030
    THEN 'CURRENT RATIO'
    WHEN itemId = 4121
    THEN 'QUICK RATIO'
    WHEN itemId = 4177
    THEN 'ASSET TURNOVER'
    WHEN itemId = 4082
    THEN 'INVENTORY TURNOVER'
  END itemname in
  from item i
  join itemcode ic on i.itemcode = ic.itemcode
  WHERE ic.dataItemId IN (.................);

我可以用任何其他方式做到这一点吗？我可以改进它吗？如何删除 WHEN-THEN？

Answer 1

我猜问题出在案例末尾的 in 中:

SELECT . . .
       END itemname in
--------------------^
from item i

您的特定case 表达式没问题； Postgres 很容易解析它。

如果你愿意，你可以使用这样的方式进行查找:

with lookup as (
     values ( 339, 'Revenue'),
            (  28, 'Revenue'),
            . . .
    )
select
from . . . left join
     lookup l
     on l.itemId = ?.itemId  -- put the table name in for itemId

或者——这是最合理的——为名称在 item 表中添加一列。这才是真正属于它的地方。