我很难从12月(上个月的第3个月)开始抓到行。我试图计算在一定时间内销售的产品数量。这是我当前的查询:

 SELECT
   a.id,
   a.default_code,
(
   SELECT SUM(product_uom_qty)
   AS
   "Total Sold"
   FROM
   sale_order_line c
   WHERE
   c.product_id = a.id
),
(
   SELECT SUM(product_uom_qty)
   AS
   "Month 3"
   FROM sale_order_line c
   WHERE
   c.product_id = a.id
   AND
   MONTH(c.create_date) = MONTH(CURRENT_DATE - INTERVAL '3 Months')
   AND
   YEAR(c.create_date) = YEAR(CURRENT_DATE - INTERVAL '3 Months')
)
FROM
   product_product a

DB就是这样的:
sale_order_line
product_id product_uom_qty  create_date
33         230              2014-07-01 16:47:45.294313

product_product
id  default_code
33  WHDXEB33

这是我收到的错误:
ERROR:  function month(timestamp without time zone) does not exist
LINE 21:    MONTH(c.create_date) = MONTH(CURRENT_DATE - INTERVAL

有什么能帮我指出正确的方向吗?

最佳答案

使用date_trunc()计算时间戳界限:

SELECT id, default_code
    , (SELECT SUM(product_uom_qty)
        FROM   sale_order_line c
        WHERE  c.product_id = a.id
       ) AS "Total Sold"
    , (SELECT SUM(product_uom_qty)
        FROM   sale_order_line c
        WHERE  c.product_id = a.id
        AND    c.create_date >= date_trunc('month', now()) - interval '2 month'
        AND    c.create_date <  date_trunc('month', now()) - interval '1 month'
      ) AS "Month 3"
FROM   product_product a;

要获得十二月(现在是二月),请使用以下表达式:
    AND    c.create_date >= date_trunc('month', now()) - interval '2 month'
    AND    c.create_date <  date_trunc('month', now()) - interval '1 month'

date_trunc('month', now())生成“2015-02-01 00:00”,减去2个月后,得到“2014-12-01 00:00”。所以,“3个月”可能是骗人的。
此外,请确保使用sargable表达式(如演示)以获得更快的性能并允许使用索引。
选择
根据实际的数据库设计和数据分布,这可能更快:
SELECT a.id, a.default_code, c."Total Sold", c."Month 3"
FROM   product_product a
LEFT  JOIN (
   SELECT product_id AS id
        , SUM(product_uom_qty) AS "Total Sold"
        , SUM(CASE WHEN c.create_date >= date_trunc('month', now()) - interval '2 month'
                   AND  c.create_date <  date_trunc('month', now()) - interval '1 month'
              THEN product_uom_qty ELSE 0 END) AS "Month 3"
   FROM   sale_order_line
   GROUP  BY 1
   ) c USING (id);

因为您正在选择所有行,所以这可能比相关子查询更快。在这方面,在你加入之前先汇总一下,这样比较便宜。
当选择一个或几个产品,这实际上可能会慢,虽然!比较:
Aggregate a single column in query with many columns
Optimize GROUP BY query to retrieve latest record per user
或者在9.4+后加上FILTER子句:
...
        , SUM(product_uom_qty)
             FILTER (WHERE c.create_date >= date_trunc('month', now()) - interval '2 month'
                     AND   c.create_date <  date_trunc('month', now()) - interval '1 month'
                    ) AS "Month 3"
...

细节:
Select multiple row values into single row with multi-table clauses

关于sql - 前三个月的值总和,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28636191/

10-10 14:06
查看更多