Início / dba / Perguntas / 149930
Accepted
DecoderReloaded
Asked: 2016-09-17 22:37:38 +0800 CST

Como obter o status start_date e end_date da tabela status_track?

  • 772

Eu tenho duas mesas:

  1. things:

    +------+----------------+------------+
| id   | current_status | created_at |
+------+----------------+------------+
| 7770 | active         | 2016-08-09 |
+------+----------------+------------+

  2. thing_status_tract:

    +----------+-------------+-----------+---------------------+
| thing_id | status_from | status_to | changed_at          |
+----------+-------------+-----------+---------------------+
|     7770 | incomplete  | inactive  | 2016-08-09 16:26:22 |
|     7770 | inactive    | active    | 2016-08-10 12:31:04 |
+----------+-------------+-----------+---------------------+

Preciso de dados no seguinte formulário. Esta tabela tem um status e seu carimbo de data/hora inicial e final correspondente para um determinado thing_id:

+----------+-------------+---------------------+---------------------+
| thing_id | status      | status_start_date   | status_end_date     |
+----------+-------------+---------------------+---------------------+
|     7770 | incomplete  | 2016-08-09 00:00:00 | 2016-08-09 16:26:22 |
|     7770 | inactive    | 2016-08-09 16:26:22 | 2016-08-10 12:31:04 |
|     7770 | active      | 2016-08-10 12:31:04 | now()               |
+----------+-------------+---------------------+---------------------+

Como fazer isso com uma consulta SQL?

mysql oracle

2 respostas

  1. Best Answer

    Claro, minha resposta anterior foi apenas para responder à sua pergunta imediata. Como mencionei em meus comentários, seu próprio modelo de dados é falho. Não apenas você não deve manter 'status_from' (prevous_status) e 'status_to' na tabela de histórico, como também NÃO deve manter o status na tabela THINGS. Isso viola as regras de normalização de dados padrão.

    O que você deve fazer é descartar o status da tabela THINGS, estabelecer um relacionamento FK entre as duas tabelas e usar uma consulta adequada para derivar o status atual quando necessário.

    Primeiro, crie e preencha a tabela THINGS. Adicionei algumas colunas apenas para indicar os tipos de coisas que devem estar nesta tabela:

    S

    QL> create table things (thing_id number,
  2                       attrib_a varchar2(10),
  3                       attrib_b varchar2(10),
  4                         constraint thing_pk primary key (thing_id)
  5                      )
  6  ;

Table created.

SQL> insert into things
  2     values (7770,
  3             'red',
  4             'white'
  5            )
  6  ;

1 row created.

SQL> insert into things
  2     values (8880,
  3             'blue',
  4             'green'
  5            )
  6  ;

1 row created.

    Em seguida, crie e preencha a tabela de rastreamento de histórico:

    SQL> create table thing_status
  2       (thing_id number,
  3        new_status  varchar2(10),
  4        status_date date,
  5          constraint fk_things
  6            foreign key (thing_id)
  7            references things(thing_id)
  8        )
  9  ;

Table created.

SQL> -- -----------------------------------------------------
SQL> insert into thing_status
  2     values (7770,
  3             'incomplete',
  4             to_date('2016-08-09 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (7770,
  3             'inactive',
  4             to_date('2016-08-10 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (7770,
  3             'active',
  4             to_date('2016-08-11 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> -- -----------------------------------------------------
SQL> insert into thing_status
  2     values (8880,
  3             'incomplete',
  4             to_date('2016-08-12 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'inactive',
  4             to_date('2016-08-13 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'active',
  4             to_date('2016-08-14 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'expired',
  4             to_date('2016-08-15 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

    E seu relatório de histórico (como antes)

    SQL> -- -----------------------------------------------------
SQL> --  Select status history
SQL> --
SQL> select thing_id,
  2         new_status,
  3         to_char(status_date,'dd-Mon-yyyy hh24:mi:ss') status_start_date,
  4         lead(to_char(status_date,'yyyy-mm-dd hh24:mi:ss'),1)
  5           over (partition by thing_id order by status_date) status_end_date
  6  from thing_status
  7  order by thing_id,
  8           status_date
  9  ;

  THING_ID NEW_STATUS STATUS_START_DATE    STATUS_END_DATE
---------- ---------- -------------------- -------------------
      7770 incomplete 09-Aug-2016 00:00:00 2016-08-10 16:26:22
      7770 inactive   10-Aug-2016 16:26:22 2016-08-11 12:32:04
      7770 active     11-Aug-2016 12:32:04
      8880 incomplete 12-Aug-2016 00:00:00 2016-08-13 16:26:22
      8880 inactive   13-Aug-2016 16:26:22 2016-08-14 12:32:04
      8880 active     14-Aug-2016 12:32:04 2016-08-15 12:32:04
      8880 expired    15-Aug-2016 12:32:04

7 rows selected.

    E seu relatório de status atual

    SQL> -- -----------------------------------------------------
SQL> --  Select current status
SQL> --
SQL> select t.thing_id,
  2         t.attrib_a,
  3         t.attrib_b,
  4         s.new_status curr_status,
  5         to_char(s.status_date,'yyyy-mm-dd hh24:mi:ss') status_date
  6  from things t
  7   join thing_status s
  8     on t.thing_id = s.thing_id
  9  where s.status_date = (select max(status_date)
 10                         from thing_status x
 11                         where s.thing_id = x.thing_id
 12                         group by thing_id
 13                        )
 14  order by t.thing_id
 15  ;

  THING_ID ATTRIB_A   ATTRIB_B   CURR_STATU STATUS_DATE
---------- ---------- ---------- ---------- -------------------
      7770 red        white      active     2016-08-11 12:32:04
      8880 blue       green      expired    2016-08-15 12:32:04
    • 4 
  2. SQL> create table thing_status_tract (thing_id number,
  2        new_status  varchar2(10),
  3        status_date date
  4       )
  5  ;

Table created.

SQL> insert into thing_status_tract
  2  values (7770,
  3  'incomplete',
  4  to_date('2016-08-09 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> insert into thing_status_tract
  2  values (7770,
  3  'inactive',
  4  to_date('2016-08-09 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> insert into thing_status_tract
  2  values (7770,
  3  'active',
  4  to_date('2016-08-10 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> insert into thing_status_tract
  2  values (8880,
  3  'incomplete',
  4  to_date('2016-08-09 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> insert into thing_status_tract
  2  values (8880,
  3  'inactive',
  4  to_date('2016-08-09 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> insert into thing_status_tract
  2  values (8880,
  3  'active',
  4  to_date('2016-08-10 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> select thing_id,
  2      new_status,
  3      to_char(status_date,'dd-Mon-yyyy hh24:mi:ss') status_start_date,
  4      lead(to_char(status_date,'yyyy-mm-dd hh24:mi:ss'),1)
  5        over (partition by thing_id order by status_date) status_end_date
  6  from thing_status_tract
  7  order by thing_id,
  8        status_date;

  THING_ID NEW_STATUS STATUS_START_DATE    STATUS_END_DATE
---------- ---------- -------------------- -------------------
      7770 incomplete 09-Aug-2016 00:00:00 2016-08-09 16:26:22
      7770 inactive   09-Aug-2016 16:26:22 2016-08-10 12:32:04
      7770 active     10-Aug-2016 12:32:04
      8880 incomplete 09-Aug-2016 00:00:00 2016-08-09 16:26:22
      8880 inactive   09-Aug-2016 16:26:22 2016-08-10 12:32:04
      8880 active     10-Aug-2016 12:32:04

6 rows selected.
    • 0

relate perguntas