postgreSQL查询 - 如何使用案例?

Question

嗨，我的查询遇到了一些问题 - 数据过滤问题 - 不知道该怎么做。

select lo.customer_no as station_num, lo."name" as station_name, fb.aggregation_type as data_source, fbh.balance_date as balance_day,
fbd.fuel_dis as fuel_dis, fb.sales_pos_manual as fuel_manual, p.product_name as product
from fuel_balance_data fbd
join fuel_balance fb on fb.id=fbd.fuel_balance_id 
join "location" lo on lo.id = fb.location_id 
join tank t on t.id = fb.tank_id 
join fuel_balance_header fbh on fbh.id=fuel_balance_header_id 
join product p on p.id=t.product_id
where fbh.location_id in (1031, 1035, 1026, 1024, 1008, 1009, 1006, 913, 320, 1010, 888, 417, 416, 440, 408, 486, 483, 402, 398, 
395, 393, 392, 391, 538, 386, 384, 383, 1033, 382, 377, 1030, 1034, 371, 369, 368, 365, 364, 363, 424, 464, 360, 357, 354, 353, 351, 347, 346, 1021, 343, 1002, 340, 1005, 445)
and fbd.gross_net = 'GROSS'
and cast(fbh.balance_date as date) between '2020-01-01' and  current_date - integer '1' 
order by balance_date, lo.customer_no, fb.aggregation_type desc

表如下所示：

+----------+---------------------------+----------+---------------------+-----------------+-----------+------------+ |station_num|station_name |data_source |balance_day |fuel_dis |fuel_manual |产品展示 | +----------+---------------------------+----------+---------------------+-----------------+-----------+------------+ |0000XXXX |LOCATION______ - 0000XXXX |手册 |2020-01-26 00：00：00 |         (空( |(空( |柴油副总裁 | |0000XXXX |LOCATION______ - 0000XXXX |手册 |2020-01-26 00：00：00 |         (空( |(空( |SFS柴油机 | |0000XXXX |LOCATION______ - 0000XXXX |手册 |2020-01-26 00：00：00 |         (空( |(空( |SFS 95 | |0000XXXX |LOCATION______ - 0000XXXX |手册 |2020-01-26 00：00：00 |         (空( |(空( |PB VPR 100 | |0000XXXX |LOCATION______ - 0000XXXX |手册 |2020-01-26 00：00：00 |         (空( |(空( |PB 95 副总裁 | |0000XXXX |LOCATION______ - 0000XXXX |汽车 |2020-01-26 00：00：00 |1001.8800000000 |(空( |SFS 95 | |0000XXXX |LOCATION______ - 0000XXXX |汽车 |2020-01-26 00：00：00 |   0.0000000000 |(空( |柴油副总裁 | |0000XXXX |LOCATION______ - 0000XXXX |汽车 |2020-01-26 00：00：00 | 993.5300000000 |(空( |SFS柴油机 | |0000XXXX |LOCATION______ - 0000XXXX |汽车 |2020-01-26 00：00：00 |   0.0000000000 |(空( |PB VPR 100 | |0000XXXX |LOCATION______ - 0000XXXX |汽车 |2020-01-26 00：00：00 |   0.0000000000 |(空( |PB 95 副总裁 | +----------+---------------------------+----------+---------------------+-----------------+-----------+------------+

这是我想要的：

1. 过滤我的查询 - 如果来自 DIS 的销售额不为空，我只想显示每天的自动
2. 过滤我的查询 - 如果来自 DIS 的销售额为空，来自现金的销售额不为空 - 我只想显示每天的手动
3. 过滤和编辑表格 - 如果在这两种情况下，来自 DIS 的销售额和来自现金的销售额都为空，我只想每天显示手动并将手动更改为no_data

在这种情况下如何使用适当的案例陈述？因为我想查询整个月的数据 - 有些日子只有自动，有些日子只有手动，有些天no_data。

Answer 1

似乎你想要：

1. 从fuel_dis和fuel_manual而不是aggregation_type列中评估的aggregation_type/data_source。
2. 每个位置和每天的最佳评估类型的排名，并且仅保留最佳排名。

对于评估，请使用CASE WHEN。对于排名，您可以使用RANK.在您的情况下，您还可以按字母顺序对类型进行排名，因为您希望"自动"优先于"手动"，"手动"优先于"NO_DATA"。我展示了这两种方法。

查询：

select *
from
(
select
datasourced.*,
-- either:
min(real_data_source) over (partition by fbh.balance_date, lo.customer_no)
as best_real_data_source,
-- or:
rank() over (partition by fbh.balance_date, lo.customer_no order by real_data_source)
as rnk
from
(
select 
lo.customer_no as station_num,
lo."name" as station_name,
fbh.balance_date as balance_day,
fbd.fuel_dis as fuel_dis,
fb.sales_pos_manual as fuel_manual,
p.product_name as product,
case when fuel_dis is not null then 'AUTO'
when fuel_manual is not null then 'MANUAL'
else 'NO_DATA'
end as real_data_source
from fuel_balance_data fbd
join fuel_balance fb on fb.id=fbd.fuel_balance_id 
join "location" lo on lo.id = fb.location_id 
join tank t on t.id = fb.tank_id 
join fuel_balance_header fbh on fbh.id=fuel_balance_header_id 
join product p on p.id=t.product_id
where fbh.location_id in (1031, 1035, 1026, 1024, 1008, 1009, 1006, 913, 320, 1010,
888, 417, 416, 440, 408, 486, 483, 402, 398, 395, 393, 392,
391, 538, 386, 384, 383, 1033, 382, 377, 1030, 1034, 371,
369, 368, 365, 364, 363, 424, 464, 360, 357, 354, 353, 351,
347, 346, 1021, 343, 1002, 340, 1005, 445)
and fbd.gross_net = 'GROSS'
and fbh.balance_date >= '2020-01-01'
and fbh.balance_date < current_date
) datasourced
) ranked
where 
-- either:
real_data_source = best_real_data_source
-- or:
rnk = 1
order by balance_date, customer_no;

(我已将您的日期子句替换为日期时间常见的>=和<。这可能使 DBMS 能够使用索引并加快访问速度。

Answer 2

select 
CASE when ISNULL([sales from dis],'')<>'' then 'only auto per day'
WHEN ISNULL([sales from dis],'')='' and ISNULL([sales from cash],'')<>'' then 'only 
manual per day'
WHEN ISNULL([sales from dis],'')='' and ISNULL([sales from cash],'')='' then 'only 
manual per day and change manual to no_data' end as [Result],* 
from
(select 
lo.customer_no as [location number],
lo."name" as [location name],
fb.aggregation_type as [datatype(man/auto)],
fbh.balance_date,
fbd.fuel_dis as [sales from dis],
fb.sales_pos_manual as [sales from cash],
p.product_name as [product type] 
from fuel_balance_data fbd 
join fuel_balance fb on fb.id=fbd.fuel_balance_id  
join "location" lo on lo.id = fb.location_id  
join tank t on t.id = fb.tank_id  
join fuel_balance_header fbh on fbh.id=fuel_balance_header_id  
join product p on p.id=t.product_id 
where fbh.location_id  = 357
and fbd.gross_net = 'GROSS' 
and fbh.balance_date  < current_date - integer '1' 
order by balance_date desc)A