我需要查询以将一个星期六的总销售额与该年度其余平均星期六的总销售额进行比较 [英] I need a query to compare one Saturday's total sales with the rest of the year's average Saturday's total sales

问题描述

我的数据集的字段是ts，数量，单价

My data set's fields are ts, quantity, unit_price

我首先需要运行sum（quanitiy * unit_price）以获得我的销售号码

I first need to run sum(quanitiy * unit_price) to get my sales number

ts（时间戳）的格式如下-2019-01-15 14:55:00 UTC

ts(time stamp) is formatted like this - 2019-01-15 14:55:00 UTC

推荐答案

这是您想要的吗？

select avg(case when datecol = ? then total end) as sales_your_date,
       avg(case when datecol <> ? then total end) as sales_other       
from (select date(t.ts) as dte, sum(t.quantity * t.unit_price) as total
      from t
      where ts >= timestamp('2018-01-01') and
            ts < timestamp('2019-01-01')
      group by dte
     ) t
where extract(dayofweek from datecol) = 6  -- Saturday

此与您之前的问题没有太大不同。

This is not much different from your previous question. The same idea works, just with aggregating the data first.

？是您关心的日期。

这篇关于我需要查询以将一个星期六的总销售额与该年度其余平均星期六的总销售额进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！