首页 文章

具有条件的递归选择

提问于
浏览
0

我的问题如下:

  • 我需要检查select中的一个值( average )是否为空

  • 如果是,运行select以查找不同时段(日期)中的第一个非空值 .

  • 在此之后,执行操作以检查(日期 - 范围)的时间段,这是

  • 之后做平均值

人类语言

我希望得到 average cost of all products ,按期间分组 .

如果在特定时期内,产品没有发票,我需要获得上一期间的平均值(如果有平均成本) .

期间 aren't months ,它们由客户定义,可以重叠2个月,例如:

2012-01-01 - 2012-01-29
2012-01-30 - 2012-02-27

我怎么能这样做 in ONE query
查询或多或少如下( average 是我要比较值的列):

select
    p.id
    ,(select 
        avg(cost)
    from 
        invoices i 
    where 
        i.product_id = p.id 
        and i.add_date between $start_date
        and $end_date
    ) as average
from
    products p;

Tables / DATA / Querys

看到这个要点(它不是原始数据库,我现在进行此测试):https://gist.github.com/4520123

3 回答

  • 0

    解决问题的另一种方法是:

    • 使用可用数据查找最新月份 .

    • 从第一步获取当月的平均值() .

    查询:

    SELECT i.product_id, 
           max(date_trunc('month',i.add_date)) as last_month
    FROM invoices i 
    GROUP BY i.product_id
    

    将在上个月为您提供每种产品的数据 .

    然后:

    SELECT p.id,
           avg(inv.cost)
    FROM products p
    JOIN invoices inv 
      ON inv.product_id = p.id
    JOIN (SELECT i.product_id, 
                 max(date_trunc('month',i.add_date)) as last_month
          FROM invoices i 
          GROUP BY i.product_id) last_inv 
      ON last_inv.product_id = inv.product_id
      AND last_inv.last_month = date_trunc('month',inv.add_date)
    

    获得上个月的 avg .

  • 1

    我想我明白了 . 您希望连续检查不同时期的平均值 . 以下是三个时期的示例:

    select p.id,
           coalesce(cost_period1, cost_period2, cost_period3) as average
    from products p left outer join
         (select i.product_id, 
                 avg(case when i.add_date between $start_date1 and $end_date1 then cost
                     end) as cost_period1,
                 avg(case when i.add_date between $start_date2 and $end_date2 then cost
                     end) as cost_period2,
                 avg(case when i.add_date between $start_date3 and $end_date3 then cost
                     end) as cost_period3
          from invoices i
          group by i.product_id
         ) ip
         on p.id = ip.product_id
    

    这是一个未经测试的查询 . 计算子查询中每个句点的平均值,然后选择第一个非NULL值 .

    根据您的评论,您只需将其转换为每个月的单独行 . 这是一种典型的方式 . . . 按年和月分组,然后选择最新的可用 .

    select p.id, avgcost
    from products p left outer join
         (select ip.*, row_number() over (partition by product_id order by yearmon desc) as seqnum
          from (select i.product_id, year(add_date)*12+month(add_date) as yearmon,
                       avg(cost) as avgcost
                from invoices i
                group by i.product_id, year(add_date)*12+month(add_date)
               ) ip
          where seqnum = 1
         ) ip
         on p.id = ip.product_id
    
  • 0

    我在我的共同工作朋友的帮助下使用此查询解决了这个问题 .

    我做的是,我拿了 last purchase (invoice) ,具体的产品(饮料)并计算了平均值 .

    select (sum(aux.price * aux.quantity) / sum(aux.quantity))
        from (select    inp.price, inp.quantity, prd.product, drk.drink_num, inv.add_date
                            from    invoices                                inv
                inner join invoice_products inp on inp.invoice = inv.invoice
                inner join products                 prd on prd.product = inp.product
                inner join drinks                       drk on drk.product = prd.product) aux,
        date_period dtp
        where 
                aux.add_date between dtp.starting_date and dtp.ending_date
                and aux.drink_num = 1836 -- example id
                and dtp.year <= 2012 -- current year search
        group by
                dtp.year,
                dtp.period
        order by 
                dtp.year desc,
                dtp.period desc
        limit 1
    

    无论如何,谢谢你们!

相关问题