计算天数，有多少天仍然有效的报价在Oracle Sql

dohp0rv5 于 2023-02-03 发布在 Oracle

关注(0)|答案(2)|浏览(110)

我想计算的天数，有多少天仍然活跃的报价。
这是我的表格：

CREATE TABLE myTable(u_id, , offer, status,status_date) as
SELECT 1,  'Offer_1', 'Active', TO_DATE('2021/12/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss')
  FROM dual
UNION ALL
SELECT 1, 'Offer_1', 'Deactive', TO_DATE('2022/02/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss') FROM dual
UNION ALL
SELECT 1, 'Offer_1','Active', TO_DATE('2022/03/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss')  FROM dual
UNION ALL
SELECT 1, 'Offer_1','Deactive' TO_DATE('2022/04/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss')  FROM dual
UNION ALL
SELECT 1, 'Offer_2','Active', TO_DATE('2022/12/30 21:02:44', 'yyyy/mm/dd hh24:mi:ss') FROM dual

这就是我的剧本：

select distinct u_id,offer_id,
trunc(nvl(case when status = 'Deactive' then status_date end),sysdate) - 
trunc(case when status = 'Active' then status_date end) date_diff 
from myTable

但它给了我错误的结果。
预期产出：
| 用户标识符|要约|总天数|
| - ------|- ------|- ------|
| 1个|报价_1|九十三|
| 1个|报价_2|三十一|

oracle

来源：https://stackoverflow.com/questions/75283387/count-the-number-of-days-how-many-days-remained-active-on-an-offer-in-oracle-sq

2条答案

按热度按时间

xdnvmnnf1#

您可以按以下步骤操作：

select u_id, offer, sum(case when status = 'Active' then ceil(trunc(sysdate) - status_date) else ceil(status_date - trunc(sysdate))  end) as total_day
from myTable
group by u_id, offer

结果：

U_ID    OFFER   TOTAL_DAY
1       Offer_1  460
1       Offer_2  31

ceil用于将指定的数字向上舍入（本例中为天数）
demo here

赞(0）回复(0）举报 2023-02-03

wixjitnu2#

假设"活动"和"停用"总是交替的，您可以使用LEAD来获得每个活动日期的描述日期。最后进行汇总和求和。

select
  u_id,
  offer,
  trunc(sum(cast(next_status_date as date) - cast(status_date as date))) as total_days
from
(
  select
    u_id, offer, status, status_date,
    coalesce
    (
      lead(status_date) over (partition by u_id, offer order by status_date),
      sysdate
    ) as next_status_date
  from mytable
)
where status = 'Active'
group by u_id, offer
order by u_id, offer;

演示：https://dbfiddle.uk/YDVTqEiR

赞(0）回复(0）举报 2023-02-03

我来回答

计算天数，有多少天仍然有效的报价在Oracle Sql

2条答案

相关问题

热门标签

最新问答