Pgsql-如何使用pgsql过滤报告天数？

Question

假设我有一个表事务，它具有如下数据：

事务

| id         | user_id     | amount      | created_at   |
|:-----------|------------:|:-----------:| :-----------:|
|    1       |        1    |     100     |  2021-09-11  |  
|    2       |        1    |     1000    |  2021-09-12  |
|    3       |        1    |     -100    |  2021-09-12  |
|    4       |        2    |     200     |  2021-10-13  |
|    5       |        2    |     3000    |  2021-10-20  |
|    6       |        3    |     -200    |  2021-10-21  |

• 我想通过以下方式过滤这些数据：持续4天，15天，28天

注意：，如果用户单击选择选项4天，这将过滤4天。

我想要这些数据

1. 佣金总额(所有交易金额之和* 5%)
2. 自上而下
3. 首次登场总额:多少(-)

请帮助我，并为基本问题感到抱歉！

预期结果：

**如果用户筛选持续4天：

假设现在的日期是: 2021-09-16

结果：

- TotalCommission (1000 - 100) * 5
- TotalTopUp: 1000
- TotalDebut: -100

Answer 1

我怀疑你想：

SELECT SUM(amount) * 0.05 AS TotalCmomission,
       SUM(amount) FILTER (WHERE amount > 0) AS TotalUp,
       SUM(amount) FILTER (WHERE amount < 0) AS TotalDown
FROM t
WHERE created_at >= CURRENT_DATE - 4 * INTERVAL '1 DAY';

这假设没有未来的created_at (这似乎是一个合理的假设)。您可以用您想要的任何值替换4。

Answer 2

看看聚合函数sum、max和min。最后四天应该是这样的：

SELECT 
  sum(amount)*.05 AS TotalComission,
  max(amount) AS TotalUp,
  min(amount) AS TotalDebut
FROM t
WHERE created_at BETWEEN CURRENT_DATE-4 AND CURRENT_DATE;

演示：db<>fiddle

Answer 3

您的描述指示指定要处理的天数，从预期的结果中可以看出您正在通过user_id查找结果(用户1可能不在此范围内)。也许最好的选择是将查询包装到SQL函数中。然后，由于您的所有数据都进入了未来，您还需要将其参数化。结果是：

create or replace 
function Commissions( user_id_in     integer default null
                    , days_before_in integer default 0
                    , end_date_in    date    default current_date
                    )
 returns table( user_id         integer
              , totalcommission numeric
              , totalup         numeric
              , totaldown       numeric
              ) 
 language sql
as $$
    select user_id 
         , sum(amount) * 0.05
         , sum(amount) filter (where amount > 0) 
         , sum(amount) filter (where amount < 0) 
      from transaction
     where (user_id = user_id_in or user_id_in is null) 
       and created_at <@ daterange( (end_date_in - days_before_in * interval '1 day')::date 
                                  , end_date_in
                                  , '[]'::text          -- indicates inclusive of both dates
                                  )
    group by user_id;
$$; 

见这里的演示。您可能只想使用参数并查看结果。