时间戳

首页 > 解决方案 > 在固定开始日期的情况下跟踪动态的每周和每月销售/采购

sql - 在固定开始日期的情况下跟踪动态的每周和每月销售/采购

问题描述

我是 Oracle SQL 编程的新手（实际上是一般编程），最近发现了分析函数。我确信我可以使用它们来自动化我的每日、每周和每月报告。

我需要帮助跟踪每周和每月的销售和购买给定的特定日期作为一年或和/或一周的开始。

我有一个表格，用于维护销售代理的日常销售和采购交易，我如何动态跟踪我们的交易中每周以及每个地区每个地区的月度采购收入增长（自年初以来）桌子。

我们的报告周从周三到周二。我已经设法逐月获得一些输出（尽管不完全准确），但每周都在挑战我。如何在 Oracle SQL 中维护一个动态的天数计数器，每次它在整个每日事务表中增加 7 天时，它会刷新并开始另一个星期？

我脑海中的一个有趣的挑战是我似乎无法将其放入代码中，当我拥有部分一周的数据时会发生什么？！我希望能够将本周 3 天的交易量与前一周的 3 天交易量进行比较。对于部分月份的数据也可以这样说。

这是我迄今为止管理的月度分析代码。

WITH

monthly_revenue as (

SELECT
to_char(txn_date, 'YYYY-MM') as month_key,
sum(stock_purchased) as revenue
FROM transactions_table
GROUP BY  to_char(date_key, 'YYYY-MM')
),

prev_month_revenue as (

SELECT 
month_key, revenue, lag(revenue) over (order by month_key) as 
prev_month_revenue
FROM monthly_revenue
)

SELECT month_key,revenue,prev_month_revenue, round(100.0*(revenue- 
prev_month_revenue)/prev_month_revenue,1) as revenue_growth
FROM prev_month_revenue

ORDER BY month_key;

我的表结构如下：

txn_date DATE, agent_id NUMBER(12), supervisor_id NUMBER(12), stock_purchased NUMBER(15), stock_sold NUMBER(15), no_of_txns NUMBER(15), account_balance NUMBER(15)

我希望我的输出格式如下；

Week-Start | Week-End | Week_Purchases | Previous_Week_Purchases | % Growth

如果我能克服跟踪每周采购和销售的最初障碍，我可以很容易地附上位置信息。

标签： sqloracle

解决方案

trunc 函数将日期截断为指定的单位。默认情况下，这是一天。但您也可以使用它来获取前一周/月/季度/年的开始。

格式 iw 返回 ISO 周的开始。这是星期一。

那么，这对您在周三至周二运行数周有何帮助？

在将其传递给 trunc 之前，从您的日期中减去两个，瞧！

with rws as (
  select date'2018-07-24'+level dt from dual
  connect by level <= 14
)
  select * from rws;

DT            
25-JUL-2018   
26-JUL-2018   
27-JUL-2018   
28-JUL-2018   
29-JUL-2018   
30-JUL-2018   
31-JUL-2018   
01-AUG-2018   
02-AUG-2018   
03-AUG-2018   
04-AUG-2018   
05-AUG-2018   
06-AUG-2018   
07-AUG-2018 

with rws as (
  select date'2018-07-24'+level dt from dual
  connect by level <= 14
)
  select trunc ( dt-2, 'iw' ),
         to_char ( min ( dt ), 'DY' ) week_start_day,
         to_char ( max ( dt ), 'DY' ) week_end_day
  from   rws
  group  by trunc ( dt-2, 'iw' )
  order  by trunc ( dt-2, 'iw' );

TRUNC(DT-2,'IW')   WEEK_START_DAY   WEEK_END_DAY   
23-JUL-2018        WED              TUE            
30-JUL-2018        WED              TUE

关于：

我希望能够将本周交易的 3 天与前一周交易的 3 天进行比较

我不确定你在这里问什么。但是您可以使用分析函数的窗口子句来获取与当前值处于特定偏移量的值。例如，以下计算两个运行总计。过去三天的第一次。第二个对应前一周的三天：

with rws as (
  select date'2018-07-24'+level dt , 
         round ( dbms_random.value( 1, 100 ) ) val
  from   dual
  connect by level <= 10
)
  select dt, val, 
         sum ( val ) over ( 
           order by dt range between 3 preceding and current row 
         ) past_three, 
         sum ( val ) over ( 
           order by dt range between 10 preceding and 7 preceding 
         ) three_prev_week
  from   rws
  order  by dt;

DT            VAL   PAST_THREE   THREE_PREV_WEEK   
25-JUL-2018       5            5            <null> 
26-JUL-2018      89           94            <null> 
27-JUL-2018      34          128            <null> 
28-JUL-2018      88          216            <null> 
29-JUL-2018      48          259            <null> 
30-JUL-2018      25          195            <null> 
31-JUL-2018      19          180            <null> 
01-AUG-2018      71          163                 5 
02-AUG-2018      12          127                94 
03-AUG-2018      39          141               128

推荐阅读