The cost of each product in the last three months

Keyword

nested subqueries,General aggregation analysis,cost analysis ,运营分析

correlation function

round,case when,date_format,date_trunc,sum

Use the date_trunc function to align the dates by day, then calculate the total daily expenses through the aggregation of time and product names, sort by day, arrange by the daily expenses in descending order on the basis of time through the Window function, and finally take the product display names and prices with the top six prices, and classify the rest into others, and format and display the time and expenses. Use flow chart to show that X-axis time, Y-axis cost and aggregation are listed as product names.

source :bill |
select
  day,
  round(sum(total), 3) as total,
  case
    when rnk <= 5 then ProductDetail
    else '其他'
  end as ProductDetail
from(
    select
      day,
      total,
      ProductDetail,
      rank() over(
        partition by day
        order by
          total desc
      ) as rnk
    from(
        select
          date_format(day, '%Y-%m-%d') as day,
          total,
          ProductDetail
        from(
            select
              date_trunc('day', __time__) as day,
              sum(PretaxAmount) as total,
              ProductDetail
            from
              log
            group by
              day,
              ProductDetail
            order by
              day
          )
      )
  )
group by
  day,
  ProductDetail
order by
  day
limit
  10000