我想添加最后30天的销售&30至90天的销售,重量更多(1.5)到最后30天销售。
SELECT userid, SUM(premiumdetails_netpremium)
from policydetail,
(SELECT userid AS partner_id, SUM(premiumdetails_netpremium) AS last30sale
FROM policydetail AS pd
WHERE DATE(sales_date) BETWEEN current_date - interval '30' day AND current_date
group by userid
) AS A
WHERE DATE(sales_date) BETWEEN current_date - interval '90' day AND current_date
group by userid
我认为您想要有条件的聚合:
SELECT userid,
SUM(CASE WHEN sales_date >= current_date - interval '30 day THEN 1.5 * premiumdetails_netpremium
ELSE premiumdetails_netpremium
END) as weighted_sum
FROM policydetail
WHERE sales_date >= current_date - interval '90' day
GROUP BY userid ;
请注意,这假设sales_date
将来不在。