Geek Answers Handbook

SQL query to count the number of objects in each state every day

Given a set of database records that record the date when an object enters a particular state, I would like to create a query that shows how many objects are in each state on any given date. The results will be used to generate trend reports showing how the number of objects in each state changes over time.

I have a table similar to the following that records the date when the object enters a specific state:

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

There are an arbitrary number of objects and states.

I need to create a query that returns the number of objects in each state of each date. The result will look like this:

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

I am working with an Oracle database.

, . , , :

, , .

+4
6

EntryDate Date, State, Count (Distinct ObjID) Count From Table_1 Group by EntryDate, State

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

, 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
0

. ., 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)
0

, , , . , count ( objid):

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

, ARE , , , . , :

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

, , .

, , , . , !:)

0

SQL SERVER, , :

(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://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html). , WITH, , - 0 99. , , , 100 ( 100 , )

, CTE, Data CTE

cte @tbl ( ) , . , / . , WHERE t.r = 1,

, , , , . , 0,

0

More articles:


All Articles