วิธีรับการนับสถานะทั้งหมดใน SQL Server

ฉันมีตารางที่เก็บประวัติสถานะงาน สถานะของงานสามารถเปลี่ยนแปลงได้ตลอดเวลา และทุกครั้งที่สถานะของงานเปลี่ยนแปลง รายการใหม่จะถูกสร้างขึ้นในตารางพร้อมสถานะใหม่และวันที่ที่แก้ไข

ตัวอย่าง:

Task ID| Status | Date Modified
-------+--------+---------------
100    |READY   | 2018-03-01
101    |READY   | 2018-03-01
102    |READY   | 2018-03-01
100    |RUNNING | 2018-03-02
101    |RUNNING | 2018-03-03
100    |FINISHED| 2018-03-03
102    |RUNNING | 2018-03-04
100    |READY   | 2018-03-04
101    |FINISHED| 2018-03-05
102    |FINISHED| 2018-03-07

ฉันจะค้นหาสถานะ "ผลรวมสะสม" ดังด้านล่างนี้ได้อย่างไร

Date        | READY | RUNNING | FINISHED
------------+-------+---------+----------
2018-03-01  |     3 |       0 |        0
2018-03-02  |     2 |       1 |        0
2018-03-03  |     1 |       1 |        1
2018-03-04  |     1 |       2 |        0
2018-03-05  |     1 |       1 |        1
2018-03-06  |     1 |       1 |        1
2018-03-07  |     1 |       0 |        2

ตัวอย่างเช่น ในวันที่ 07-03-2018:

  • สถานะของ Task 100 พร้อมแล้ว (อัปเดตล่าสุดเป็น READY เมื่อ 2018-03-04)
  • สถานะของ Task 101 เสร็จสิ้นแล้ว (อัปเดตล่าสุดเป็น FINISHED เมื่อ 2018-03-05)
  • สถานะของ Task 102 เสร็จสิ้นแล้ว (อัปเดตล่าสุดเป็น FINISHED เมื่อ 2018-03-07)

ดังนั้นการนับสถานะในวันที่ 2018-03-07 คือ READY:1, RUNNING:0, FINISHED:2

ฉันเจอตัวอย่างทั้งหมดที่ทำงานอยู่หลายตัวอย่างบนเว็บที่ใช้พาร์ติชั่นทับ แต่ดูเหมือนจะไม่สามารถนำไปใช้กับสถานการณ์นี้ได้


sql-server pivot
person k.t    schedule 10.06.2018    source แหล่งที่มา
comment
โดยทั่วไปผลรวมสะสมจะคำนวณตามวันที่จากน้อยไปมาก เมื่อวันที่เพิ่มขึ้น ค่าของฟิลด์จะถูกรวมเข้าด้วยกันเพื่อคำนวณผลรวมของบันทึกทั้งหมด สูงสุด จนถึงวันที่ปัจจุบัน ดูเหมือนว่าข้อมูลตัวอย่างของคุณไม่ได้แสดงให้เห็นสิ่งที่คล้ายกันนี้   -  person Giorgos Betsos    schedule 10.06.2018
comment
มันไม่ใช่ผลรวมต่อเนื่องอย่างแน่นอน แต่ความคิดก็คล้ายกัน ในแต่ละวัน ฉันต้องนับจำนวนงานในแต่ละรัฐ เนื่องจากสถานะของงานทั้งหมดอาจไม่เปลี่ยนแปลงทุกวัน เราจึงต้องค้นหาการอัปเดตสถานะของงาน ขึ้น จนถึงวันที่ปัจจุบัน (ตามลำดับจากน้อยไปมาก) เพื่อรับการนับสถานะของวันนั้น   -  person k.t    schedule 10.06.2018
comment
คุณช่วยอธิบายวิธีคำนวณจำนวนแต่ละสถานะสำหรับวันที่ที่ระบุ เช่น 2018-03-07? นอกจากนี้ เหตุใดข้อมูลตัวอย่างของคุณจึงแสดงอยู่ในลำดับ จากมากไปน้อย   -  person Giorgos Betsos    schedule 10.06.2018
comment
อ่า ฉันเดาว่า จากน้อยไปมาก ในตัวอย่างจะดีกว่า แต่มันไม่ได้เปลี่ยนแปลงอะไรจริงๆ ในวันที่ 07-03-2018: - สถานะของงาน 100 พร้อมแล้ว (อัปเดตล่าสุดเป็น READY เมื่อวันที่ 03-03-2018-04) - สถานะของงาน 101 เสร็จสิ้นแล้ว (อัปเดตล่าสุดเป็น เสร็จสิ้นเมื่อ 2018-03-05) - สถานะ ของ Task 102 เสร็จสิ้นแล้ว (อัปเดตล่าสุดเป็น FINISHED เมื่อวันที่ 2018-03-07) ดังนั้นสถานะที่นับในวันที่ 2018-03-07 คือ READY:1, RUNNING:0, FINISHED:2   -  person k.t    schedule 10.06.2018
comment
ขั้นแรกให้พิจารณาว่าคุณใช้ RDBMS ใด   -  person Strawberry    schedule 10.06.2018

คำตอบ (1)


arrow_upward
3
arrow_downward

แทนที่จะคำนวณผลรวม คุณต้องนับสถานะล่าสุดสำหรับแต่ละ ID หรือไม่

สิ่งนี้น่าจะใช้ได้ แต่คุณต้องมีตารางปฏิทิน (ของจริงหรือนับ):

select
  c.date,
  sum(case when X.status = 'Ready' then 1 else 0 end),
  sum(case when X.status = 'Running' then 1 else 0 end),
  sum(case when X.status = 'Finished' then 1 else 0 end)
from
  calendar c
  outer apply (
    select ID, Status from
    (
        select
            ID,
            row_number() over (partition by ID order by DateModified desc) RN
        from
            yourtable t
        where
            t.DateModified <= c.date
    ) X
    where X.RN = 1
  ) X
group by
  c.date

ซึ่งจะรวบรวมสถานะล่าสุดของแต่ละ ID ในแต่ละวัน แล้วคำนวณจำนวน อาจมีข้อผิดพลาดทางไวยากรณ์ แต่สิ่งนี้ควรจะใช้ได้

person Veijo    schedule 10.06.2018
comment
ทำงานให้ฉัน สาธิต SQL - person k.t; 10.06.2018