sql - PostgreSQL 检索重复的行记录
问题描述
我正在运行 PostgreSQL 查询,但它检索到我不需要的重复行记录?#请帮忙
将不胜感激;如果有人建议或改进我的原始查询,谢谢。
我使用了 distinct、groupby 和 having 子句,但它仍然检索重复的行记录。
SET statement_timeout TO 9000000;
SELECT
sp.sku
,soi.product_id
,sc.name
,sp.name
,sp.active
,sp.stock AS total_stock
,sp.actual_stock
,sp.virtual_stock
,sp.price AS current_proce
,date(sp.created::timestamp AT time zone 'utc' AT time zone 'asia/kolkata')
,CURRENT_DATE - date(sp.created::TIMESTAMP AT TIME ZONE 'utc' AT TIME ZONE 'asia/kolkata') AS days_since_created
,DATE_PART('week', date(sp.created::timestamp AT time zone 'utc' AT time zone 'asia/kolkata')) AS week
,sum(soi.quanity) AS units_sold
,((soi.price* sum(soi.quanity)) - sum(soi.discount)) AS Revenue_Before_Tax
,case
WHEN sc.name = 'Accessories' AND so.currency = 'USD' THEN ((soi.price * sum(soi.quanity) * 70) - sum(soi.discount)) - round((((soi.price*sum(soi.quanity)*70) - sum(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Apparel' AND so.currency = 'USD' THEN ((soi.price*sum(soi.quanity) * 70) - sum(soi.discount)) - round((((soi.price* sum(soi.quanity)* 70) - SUM(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Wellness' AND so.currency ='USD' THEN ((soi.price*sum(soi.quanity))*70 - sum(soi.discount)) - round((((soi.price*sum(soi.quanity) * 70) - sum(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Footwear' AND so.currency = 'USD' THEN ((soi.price*sum(soi.quanity)*70) - sum(soi.discount)) - round((((soi.price* sum(soi.quanity)*70) - SUM(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Bags' AND so.currency = 'USD' THEN ((soi.price*sum(soi.quanity)*70) - SUM(soi.discount)) - round((((soi.price* sum(soi.quanity)*70) - sum(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Jewelry'AND so.currency = 'USD' THEN ((soi.price*sum(soi.quanity*70)) - sum(soi.discount))- round((((soi.price* sum(soi.quanity)*70) - sum(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Home & Decor' AND so.currency = 'USD' THEN ((soi.price*sum(soi.quanity)*70) - sum(soi.discount)) - round((((soi.price* sum(soi.quanity)*70) - sum(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Mother & Child' AND so.currency = 'USD' THEN ((soi.price*sum(soi.quanity)*70) - sum(soi.discount)) - round((((soi.price* sum(soi.quanity)*70) - sum(soi.discount))*hsn.tax::integer)/100)
ELSE ((soi.price*sum(soi.quanity)) - sum(soi.discount)) - round((((soi.price*sum(soi.quanity)) - sum(soi.discount))*hsn.tax::integer)/100)
END AS revenue_after_tax
,round(ng.revenue_after_tax / ng.units_sold) AS average_selling_price
FROM
order_order AS so
LEFT JOIN
order_orderproduct AS soi ON so.id = soi.order_id
LEFT JOIN
store_product AS sp ON soi.product_id = sp.id
LEFT JOIN
store_category AS sc ON sp.category_id = sc.id
LEFT JOIN
tms_hsncode AS hsn ON sp.hsncode_id = hsn.id
LEFT JOIN
(SELECT
sp.sku AS gt
,sp.id
,sc.name
,sp.name
,sp.active
,sp.stock AS total_stock
,sp.actual_stock
,sp.virtual_stock
,sp.price AS current_proce
,date(sp.created::timestamp AT time zone 'utc' AT time zone 'asia/kolkata')
,CURRENT_DATE - date(sp.created::TIMESTAMP AT TIME ZONE 'utc' AT TIME ZONE 'asia/kolkata') AS days_since_created
,DATE_PART('week', date(sp.created::timestamp AT time zone 'utc' AT time zone 'asia/kolkata')) AS week
,count(soi.quanity) AS units_sold
,((soi.price* count(soi.quanity)) - sum(soi.discount)) AS Revenue_Before_Tax
,case
WHEN sc.name = 'Accessories' AND so.currency = 'USD' THEN ((soi.price * count(soi.quanity) * 70) - sum(soi.discount)) - round((((soi.price*count(soi.quanity)*70) - sum(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Apparel' AND so.currency = 'USD' THEN ((soi.price*count(soi.quanity) * 70) - sum(soi.discount)) - round((((soi.price* count(soi.quanity)* 70) - SUM(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Wellness' AND so.currency ='USD' THEN ((soi.price*count(soi.quanity))*70 - sum(soi.discount)) - round((((soi.price*count(soi.quanity) * 70) - sum(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Footwear' AND so.currency = 'USD' THEN ((soi.price*count(soi.quanity)*70) - sum(soi.discount)) - round((((soi.price* count(soi.quanity)*70) - SUM(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Bags' AND so.currency = 'USD' THEN ((soi.price*count(soi.quanity)*70) - SUM(soi.discount)) - round((((soi.price* count(soi.quanity)*70) - sum(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Jewelry'AND so.currency = 'USD' THEN ((soi.price*count(soi.quanity*70)) - sum(soi.discount))- round((((soi.price*count(soi.quanity)*70) - sum(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Home & Decor' AND so.currency = 'USD' THEN ((soi.price*count(soi.quanity)*70) - sum(soi.discount)) - round((((soi.price*count(soi.quanity)*70) - sum(soi.discount))*hsn.tax::integer)/100)
WHEN sc.name = 'Mother & Child' AND so.currency = 'USD' THEN ((soi.price*count(soi.quanity)*70) - sum(soi.discount)) - round((((soi.price*count(soi.quanity)*70) - sum(soi.discount))*hsn.tax::integer)/100)
ELSE ((soi.price*count(soi.quanity)) - sum(soi.discount)) - round((((soi.price*count(soi.quanity)) - sum(soi.discount))*hsn.tax::integer)/100)
END AS revenue_after_tax
FROM
order_order AS so
LEFT JOIN
order_orderproduct AS soi ON so.id = soi.order_id
LEFT JOIN
store_product AS sp ON soi.product_id = sp.id
LEFT JOIN
store_category AS sc ON sp.category_id = sc.id
LEFT JOIN
tms_hsncode AS hsn ON sp.hsncode_id = hsn.id
WHERE
sp.created::TIMESTAMP AT TIME ZONE 'utc' AT TIME ZONE 'asia/kolkata' >= CURRENT_DATE - 100
GROUP BY
sp.sku
,sp.id
,soi.price
,so.currency
,hsn.tax
,sc.name
,sp.name
,sp.active
,sp.stock
,sp.actual_stock
,sp.virtual_stock
,sp.price
,sp.created
,days_since_created
,week ) AS ng ON sp.sku = ng.gt
WHERE
sp.created::TIMESTAMP AT TIME ZONE 'utc' AT TIME ZONE 'asia/kolkata' >= CURRENT_DATE - 100
GROUP BY
sp.sku
,soi.product_id
,soi.price
,so.currency
,hsn.tax
,sc.name
,sp.name
,sp.active
,sp.stock
,sp.actual_stock
,sp.virtual_stock
,sp.price
,sp.created
,days_since_created
,week
,ng.revenue_after_tax
,ng.units_sold
;
我需要以下格式的输出
sku, product_id, ......................平均售价
解决方案
您可以在任何查询中使用它:
select distinct * from ($query$) $table_alias_for_query;
更好的方法应该是在原始查询中添加 distinct(我没有在您的查询中注意到 distinct)。
推荐阅读
- python - 增加 Pytorch 神经网络的数据集的 batch_size
- python - 如何修复:coreml 模型从 coremltools 转换多类 xgboost 模型产生看似随机的输出,python 3.7?
- angular - ActivatedRoute 不会在路由更改时更新子路由数据
- python - 在Python中划分数组以获得混淆矩阵
- c++ - 另一个类模板特化(在布尔参数上)
- html - 关键帧开始时意外下降
- python - 从 Windows 应用程序拦截文件路径
- java - Java:如何从第二种方法中获取变量以在第三种方法中工作?
- apache-kafka - 使用 @KafkaListener 和侦听器 ack-mode 设置为记录的 Spring Kafka 轮询
- amazon-web-services - 如何从 VSCode 更新我的 AWS Lambda 函数?