计算昨天＆＃x27之间的差异使用滞后功能销售

发布于 2025-02-06 14:42:00 字数 575 浏览 3 评论 0原文

我正在尝试获得昨天的销售与功能滞后的区别我正在使用mySQL，但我的问题是我得到了零值。

这是我的查询，

select to_char(purchase_date, 'YYYY-MM-DD') as "date", 
item,
brand,
sales
from consolidated_purchases
where purchase_date >='2022-05-05' and purchase_date <'2022-05-07'
LAG(sales,1) over (partition by "date", item, brand order by sales)

我不确定我是否正确使用了分区子句。我的目标是能够创建一个可以自动发送的报告，仅当特定产品的销售低于昨天销售的20％。

原文

I'm trying to get the difference between yesterday's sales with the function LAG
I'm using MySQL, but my problem is that I'm getting null values.

Here's my query

select to_char(purchase_date, 'YYYY-MM-DD') as "date", 
item,
brand,
sales
from consolidated_purchases
where purchase_date >='2022-05-05' and purchase_date <'2022-05-07'
LAG(sales,1) over (partition by "date", item, brand order by sales)

I'm not sure if I'm using correctly the partition clause.
My goal is to be able to create a report that can be sent automatically just when sales of a particular product are lower than 20% of yesterday's sales.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

潇烟暮雨 2025-02-13 14:42:00

很少有DBMS产品允许在同一选择子句中使用另一列使用的选择子句中定义的别名。 mySQL不允许这样做，但是修复程序很容易 - 只是不要使用该别名，只需复制/粘贴别名所指的细节即可。例如：

SELECT
      to_char(purchase_date, 'YYYY-MM-DD') AS "date"
    , item
    , brand
    , sales
    , LAG(sales, 1) OVER (
        PARTITION BY  to_char(purchase_date, 'YYYY-MM-DD') 
        , item
        , brand ORDER BY sales
        )                                 AS lag_sales
FROM consolidated_purchases
WHERE purchase_date >= '2022-05-05' AND purchase_date < '2022-05-07'

teradata是我知道的唯一允许您定义别名（例如“日期”），然后重复使用同一选择子句中的别名。可能还有其他人（有些我可能只是忘记了），但它们是少数。始终避免尝试在同一选择子句中重新使用别名，除非您知道您正在使用的DBMS明确支持这一点。

我建议您尝试以下内容，但是，我不确定您需要按照“日期”进行分区

SELECT
      to_char(purchase_date, 'YYYY-MM-DD') AS "date"
    , item
    , brand
    , sales
    , LAG(sales, 1) OVER (PARTITION BY  item, brand 
                          ORDER BY purchase_date) AS lag_sales
FROM consolidated_purchases
WHERE purchase_date >= '2022-05-05' AND purchase_date < '2022-05-07'

It is quite rare to find a dbms product that allows use of an alias defined IN the select clause to be used by another column in THE SAME select clause. MySQL does not allow this but the fix is easy - just don't use that alias, simply copy/paste the detail that the alias refers to. e.g.:

SELECT
      to_char(purchase_date, 'YYYY-MM-DD') AS "date"
    , item
    , brand
    , sales
    , LAG(sales, 1) OVER (
        PARTITION BY  to_char(purchase_date, 'YYYY-MM-DD') 
        , item
        , brand ORDER BY sales
        )                                 AS lag_sales
FROM consolidated_purchases
WHERE purchase_date >= '2022-05-05' AND purchase_date < '2022-05-07'

Teradata is the only dbms I know of that allows you define an alias (such as your "date") and then re-use that alias in the same select clause. There may well be others (some I may have simply forgotten) but they are a minority. It is easier to always avoid attempting to re-use aliases in the same select clause, unless you know that the dbms you are using explicitly supports this.

I would suggest you try the following however, I'm not sure you need to partition by the "date"

SELECT
      to_char(purchase_date, 'YYYY-MM-DD') AS "date"
    , item
    , brand
    , sales
    , LAG(sales, 1) OVER (PARTITION BY  item, brand 
                          ORDER BY purchase_date) AS lag_sales
FROM consolidated_purchases
WHERE purchase_date >= '2022-05-05' AND purchase_date < '2022-05-07'

回复收藏 0 原文

~没有更多了~