如何更改或优化此postgres sql查询，以使输出格式保持不变？

Question

原始的昂贵而缓慢的查询如下：

select 'INTEGRATED' as "STATUS", coalesce(sum(response_count),0) as "COUNT" from f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00','2015-08-05 01:00:00', null, 'LINK')
where wfc_result in ('TM_OK', 'TM_NO_CHANGE')
union
select 'WFC_FALLOUT' as "STATUS", coalesce(sum(response_count),0) as "COUNT" from f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00','2015-08-05 01:00:00', null, 'LINK')
where wfc_result LIKE 'WFC%'
union
select 'TM_FALLOUT' as "STATUS", coalesce(sum(response_count),0) as "COUNT" from f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00','2015-08-05 01:00:00', null, 'LINK')
where wfc_result = 'TM_FAIL'
union
select 'WFC_PENDING' as "STATUS", coalesce(sum(response_count),0) as "COUNT" from f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00','2015-08-05 01:00:00', null, 'LINK')
where wfc_result = 'PENDING'; 

输出：

Status       Count
Integrated     40
TM_FALLOUT      50
WFC_PENDING      60
WFC_FALLOUT      70

上面的输出格式是我所需要的。但是这个查询需要很长时间。

我想使用的查询如下，因为它花费较少的时间。

select 
sum(CASE WHEN wfc_result IN ('TM_OK','TM_NO_CHANGE') THEN response_count ELSE 0 END) as "INTEGRATED", 
sum(CASE WHEN wfc_result LIKE 'WFC%' THEN response_count ELSE 0 END) as "WFC_FALLOUT",
sum(CASE WHEN wfc_result = 'TM_FAIL' THEN response_count ELSE 0 END) as "TM_FALLOUT",
sum(CASE WHEN wfc_result = 'PENDING' THEN response_count ELSE 0 END) as "WFC_PENDING"
from f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00','2015-08-05 01:00:00',null, 'LINK')

但是，这个查询的输出如下所示，我需要与第一个查询相同的输出。

Integrated   WFC_FALLOUT   TM_FALLOUT   WFC_PENDING
40             50            60              70

我尝试了几种方法，但无法找到如何编辑这个？

Answer 1

假设“wfc_result”WFC%始终是WFC_%，溜溜球可以执行以下操作：

SELECT status.lbl AS status, coalesce(sum(response_count), 0) AS count
FROM (VALUES
  ('TM_O', 'INTEGRATED'),
  ('TM_N', 'INTEGRATED'),
  ('WFC_', 'WFC_FALLOUT'),
  ('TM_F', 'TM_FALLOUT'),
  ('PEND', 'WFC_PENDING')) status(cls, lbl)
LEFT JOIN f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00', '2015-08-05 01:00:00', NULL, 'LINK') wfc
  ON substr(wfc.wfc_result, 1, 4) = status.cls
GROUP BY 1;

如果只有几个'wfc_result‘字符串( LIKE 'WFC%' )，您还可以在VALUES子句中拼出它们(以及其他类)，然后在JOIN子句中删除substr()函数。

要获得总输出，查询要简单得多：

SELECT sum(response_count) AS total_count
FROM f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00', '2015-08-05 01:00:00', NULL, 'LINK');

当然，这会对昂贵的函数进行另一次调用，但是没有办法绕过数据库中的调用；在客户端应用程序中，您可以简单地总结上一次查询返回的"count“值。