时间戳

首页 > 解决方案 > 计算收盘百分比的窗函数

postgresql - 计算收盘百分比的窗函数

问题描述

我正在使用 PSQL 中的一个查询，我正在尝试使用一个窗口函数来划分另外两个窗口函数计数。这是我目前拥有的：

WITH month_cte as ( Select generate_series(date_trunc('month', current_date) - interval '12' month, date_trunc('month', current_date), interval '1' month) as month_year
)
select DISTINCT ON (month_year, q.rep_name) month_cte.*, q.*
FROM month_CTE
LEFT JOIN (
    select *,
        CASE
        WHEN date_quoted IS NOT NULL THEN COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', date_quoted))
        ELSE NULL
        END as month_quotes,
        CASE WHEN edocs_signed_date IS NOT NULL THEN COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', edocs_signed_date))
        ELSE NULL
        END as month_sales,
        CASE
        WHEN date_quoted IS NOT NULL And edocs_signed_date IS NOT NULL THEN CAST(COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', edocs_signed_date))/
    COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', date_quoted))* 100.0 AS numeric) 
        ELSE NULL
    END as month_closing
    FROM quote_report_view
    ORDER BY rep_name, edocs_signed_date, date_quoted
    ) q
    ON (date_trunc('month', q.date_quoted) = month_cte.month_year OR date_trunc('month', q.edocs_signed_date) = month_cte.month_year)
ORDER BY month_year, rep_name, month_quotes, month_sales

我要开始工作的线路是第三种情况：

CASE WHEN date_quoted IS NOT NULL And edocs_signed_date IS NOT NULL THEN CAST(COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', edocs_signed_date))/
        COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', date_quoted))* 100.0 AS numeric) 
            ELSE NULL
        END as month_closing

我基本上是在尝试将第二个计数窗口函数除以第一个计数窗口函数并获得月份关闭的百分比。

这些是我目前的结果：

"2020-08-01 00:00:00-04"    869272  "2020-08-04 00:00:00"   "2020-08-04 00:00:00"   "Jesus" 1   1   100.0
"2020-08-01 00:00:00-04"    875518  "2020-08-19 00:00:00"   "2020-09-01 00:00:00"   "Jim"   36  1   0.0
"2020-08-01 00:00:00-04"    876462  "2020-08-04 00:00:00"   "2020-08-04 00:00:00"   "Nick"  39  12  0.0
"2020-08-01 00:00:00-04"    873572  "2020-08-04 00:00:00"   "2020-08-04 00:00:00"   "Piero" 63  36  0.0

在我试图计算收盘百分比的最后一列中，我只得到 0.00 或 1.00。我怎样才能使这项工作得到一个真实的百分比？

谢谢！

标签： postgresql

解决方案

发生的事情是，对于 ex 使用您的数据 db 引擎首先计算除法整数/整数（ ex 1/ 36 结果为 0，数据类型为整数）然后它执行乘法 0 * 100.0 （整数 * 输出数据类型为数字数字，但结果为 0.00

  • 所以要么将第一个 count(*) 转换为数字
  • 或乘以 1.0
  • 或者如果您正在计算百分比，首先将第一个 count(*) 乘以 100.00，如下所示：
WITH month_cte as ( Select generate_series(date_trunc('month', current_date) - interval '12' month, date_trunc('month', current_date), interval '1' month) as month_year
)
select DISTINCT ON (month_year, q.rep_name) month_cte.*, q.*
FROM month_CTE
LEFT JOIN (
    select *,
        CASE
        WHEN date_quoted IS NOT NULL 
            THEN COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', date_quoted))
            ELSE NULL
        END as month_quotes,
        CASE WHEN edocs_signed_date IS NOT NULL 
            THEN COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', edocs_signed_date))
            ELSE NULL
        END as month_sales,
        CASE WHEN date_quoted IS NOT NULL And edocs_signed_date IS NOT NULL 
            THEN CAST(COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', edocs_signed_date)) * 100.0
            / COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', date_quoted)) AS numeric) 
            ELSE NULL
    END as month_closing
    FROM quote_report_view
    ORDER BY rep_name, edocs_signed_date, date_quoted
    ) q
    ON (date_trunc('month', q.date_quoted) = month_cte.month_year OR date_trunc('month', q.edocs_signed_date) = month_cte.month_year)
ORDER BY month_year, rep_name, month_quotes, month_sales

推荐阅读