我需要做一个JasperReport。我需要显示账户过程的总数,分解成weekly intervals
与number
中activated
和declined
账户。
到目前为止,对于每周间隔查询:
SELECT *
FROM account_details
WHERE DATE date_opened = DATE_ADD(2014-01-01, INTERVAL(1-DAYOFWEEK(2014-01-01)) +1 DAY)
这似乎是正确的,但不POSTGRES
正确。它一直在抱怨1-DAYOFWEEK
。这是我有望实现的目标:
更新这是非常丑陋的,但我不知道有什么更好的。Id可以完成工作。但是不知道它是否可以重构为至少看起来更好。我现在也不知道如何处理被零除的问题。
SELECT to_char(d.day, 'YYYY/MM/DD - ') || to_char(d.day + 6, 'YYYY/MM/DD') AS Month
, SUM(CASE WHEN LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END) AS Activated
, SUM(CASE WHEN LOWER(situation) LIKE '%declined%' THEN 1 ELSE 0 END) AS Declined
, SUM(CASE WHEN LOWER(situation) LIKE '%declined%' OR LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END) AS Total
, to_char( 100.0 *( (SUM(CASE WHEN LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END)) / (SUM(CASE WHEN LOWER(situation) LIKE '%declined%' OR LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END))::real) , '99.9') AS percent_activated
, to_char( 100.0 *( (SUM(CASE WHEN LOWER(situation) LIKE '%declined%' THEN 1 ELSE 0 END)) / (SUM(CASE WHEN LOWER(situation) LIKE '%declined%' OR LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END))::real) , '99.9') AS percent_declined
FROM (
SELECT day::date
FROM generate_series('2014-08-01'::date, '2014-09-14'::date, interval '1 week') day
) d
JOIN account_details a ON a.date_opened >= d.day
AND a.date_opened < d.day + 6
GROUP BY d.day;
SELECT to_char(d.day, 'YYYY/MM/DD" - "')
|| to_char(d.day + 6, 'YYYY/MM/DD') AS week
, count(situation ILIKE '%active%' OR NULL) AS activated
, ...
FROM (
SELECT day::date
FROM generate_series('2014-08-11'::date
, '2014-09-14'::date
, '1 week'::interval) day
) d
LEFT JOIN account_details a ON a.date_opened >= d.day
AND a.date_opened < d.day + 7 -- 7, not 6!
GROUP BY d.day;
相关答案:
有关计数特定值的更多信息:
撇开:您通常会使用enum
或查找表,仅存储的ID situation
,而不是多余的冗长文本。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句