oracle 用于计算每天处于每种状态的对象数量的SQL查询

2w3rbyxf  于 2023-01-25  发布在  Oracle
关注(0)|答案(8)|浏览(173)

给定一组记录对象进入特定状态的日期的数据库记录,我想生成一个查询,显示在任何特定日期处于每种状态的对象数量。结果将用于生成趋势报告,显示处于每种状态的对象数量随时间的变化情况。
我有一个如下所示的表,它记录了对象进入特定状态的日期:

ObjID EntryDate  State
----- ---------- -----
    1 2014-11-01   A
    1 2014-11-04   B
    1 2014-11-06   C
    2 2014-11-01   A
    2 2014-11-03   B
    2 2014-11-10   C
    3 2014-11-03   B
    3 2014-11-08   C

存在任意数量的对象和状态。
我需要生成一个查询,返回在每个日期处于每个状态的对象数量,结果如下所示:

Date       State Count
---------- ----- -----
2014-11-01   A       2
2014-11-01   B       0
2014-11-01   C       0
2014-11-02   A       2
2014-11-02   B       0
2014-11-02   C       0
2014-11-03   A       1
2014-11-03   B       2
2014-11-03   C       0
2014-11-04   A       0
2014-11-04   B       3
2014-11-04   C       0
2014-11-05   A       0
2014-11-05   B       3
2014-11-05   C       0
2014-11-06   A       0
2014-11-06   B       2
2014-11-06   C       1
2014-11-07   A       0
2014-11-07   B       2
2014-11-07   C       1
2014-11-08   A       0
2014-11-08   B       1
2014-11-08   C       2
2014-11-09   A       0
2014-11-09   B       1
2014-11-09   C       2
2014-11-10   A       0
2014-11-10   B       0
2014-11-10   C       3

我正在使用Oracle数据库。
我还没有找到一个与我的情况相匹配的例子。下面的问题看起来像是在问类似但不同的问题的解决方案:

任何帮助或提示,可以提供将不胜感激。

hgqdbh6s

hgqdbh6s1#

SELECT EntryDate AS "Date", State, COUNT(DISTINCT ObjectId) AS "Count" GROUP BY EntryDate, State ORDER BY EntryDate, State;
8i9zcol2

8i9zcol22#

我将用一种快速而又不太实用的方法来获取数字。您可以选择自己喜欢的方法...使用递归CTE、connect by或数字表。因此,下面的代码生成日期和州的所有组合。然后,它使用相关子查询来计算每个日期每个州的对象数量:

with n as (
      select rownum - 1 as n
      from table t
     ),
     dates as (
      select mind + n.n
      from (select min(date) as mind, max(date) as maxd from table) t
      where mind + n.n <= maxd
     )
select d.date, s.state,
       (select count(*)
        from (select t2.*, lead(date) over (partition by ObjId order by date) as nextdate
              from table t2
             ) t2
        where d.date >= t2.date and (d.date < t2.nextdate or t2.nextdate is null) and
              d.state = t2.state
       ) as counts
from dates d cross join
     (select distinct state from table t)
5w9g7ksd

5w9g7ksd3#

假设每个对象一天只改变一次状态,这个查询将列出每天有多少对象进入一个特定的状态。如果对象一天改变状态不止一次,你需要使用count(distinct objid):

select entrydate, state, count(objid) 
from my_table
group by entrydate, state
order by entrydate, state

但是,您要问的是每天有多少对象处于特定状态,因此您需要一个非常不同的查询来显示这一点。由于您在示例中只提供了该特定表,因此我将只使用该表:

select alldatestates.entrydate, alldatestates.state, count(statesbyday.objid)
from
    (
    select alldates.entrydate, allstates.state
    from (select distinct entrydate from mytable) alldates,
         (select distinct state from mytable) allstates
    ) alldatestates
    left join
    (
    select alldates.entrydate, allobjs.objid, (select min(state) as state from mytable t1 
                                          where t1.objid = allobjs.objid and 
                                                t1.entrydate = (select max(entrydate) from mytable t2 
                                                                where t2.objid = t1.objid and
                                                                      t2.entrydate <= alldates.entrydate)) as state
    from (select distinct entrydate from mytable) alldates,
         (select distinct objid from mytable) allobjs
    ) statesbyday
    on alldatestates.entrydate = statesbyday.entrydate and alldatestates.state = statesbyday.state
group by alldatestates.entrydate, alldatestates.state
order by alldatestates.entrydate, alldatestates.state

当然,如果您有一个表存储所有可能的状态,另一个表存储所有可能的对象ID,那么这个查询就会简单得多。
另外,也许你可以找到一个比这更简单的查询,但这个查询是有效的。缺点是,它可能很快成为优化器的噩梦!:)

bvjveswy

bvjveswy4#

由于每个状态不是在每个日期都记录的,因此需要执行CROSS JOIN以获得唯一的状态,然后执行GROUP BY

SELECT EntryDate, 
       C.State, 
       SUM(case when C.state = Table1.state then 1 else 0 end) as Count
FROM Table1
CROSS JOIN ( SELECT DISTINCT State FROM Table1) C
GROUP BY EntryDate, C.State
ORDER BY EntryDate
0lvr5msh

0lvr5msh5#

尝试以下查询:

select EntryDate As Date, State, COUNT(ObjID) AS Count from table_name
GROUP BY EntryDate , State 
ORDER BY State
j2cgzkjk

j2cgzkjk6#

你也可以尝试解析函数:

Select
Date,
State,
count(distinct obj) OVER (PARTITION BY EntryDate, State) count
from table
order by 1;
pokxtpni

pokxtpni7#

选择EntryDate作为日期、状态、计数(不同对象ID)作为Table_1中的计数按EntryDate、状态分组

v1l68za4

v1l68za48#

使用SQL SERVER,因为我更熟悉它,但以下是我目前所掌握的:
fiddle示例(SQL SERVER,但唯一的区别应该是日期函数,我认为...):http://sqlfiddle.com/#!3/8b9748/2

WITH zeroThruNine AS (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9), 
nums AS (SELECT 10*b.n+a.n AS n FROM zeroThruNine a, zeroThruNine b), 
Dates AS (
    SELECT DATEADD(d,n.n,(SELECT MIN(t.EntryDate) FROM @tbl t)) AS Date
    FROM nums n
    WHERE DATEADD(d,n.n,(SELECT MIN(t.EntryDate) FROM @tbl t))<=(SELECT MAX(t.EntryDate) FROM @tbl t)
), Data AS (
    SELECT d.Date, t.ObjID, t.State, ROW_NUMBER() OVER (PARTITION BY t.ObjID, d.Date ORDER BY t.EntryDate DESC) as r
    FROM Dates d, @tbl t
    WHERE d.Date>=t.EntryDate
)
SELECT t.Date, t.State, COUNT(*)
FROM Data t
WHERE t.r=1
GROUP BY t.Date, t.State
ORDER BY t.Date, t.State

首先,制作一个数字表(参见http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html),在不同的数据库中创建数字表有不同的方法,所以我创建的前两个WITH表达式只是为了创建数字0到99的视图,我相信还有其他的方法,您可能需要的数字不止100个(代表您提供的第一个和最后一个日期之间的100个日期)
因此,一旦进入日期CTE,主要部分就是数据CTE
它从Datescte中查找每个日期,并将其与@tbl表(您的表)中的值(该表包含在该日期之后记录的任何State)配对,它还按降序标记每个objid的state的顺序,这样,在最后的查询中,我们可以只使用WHERE t.r=1来获取每个objid每个日期的max state
有一个问题,它获取所有日期的数据,甚至包括那些没有记录任何内容的日期,但是对于零计数,它不会返回任何内容。如果您愿意,可以使用不同状态的视图来连接此结果,并在没有进行连接时取0

相关问题