Вопрос:

выравнивание строк таблицы оракула в одну строку с несколькими столбцами

sql oracle oracle11g dynamic-queries

1246 просмотра

3 ответа

852 Репутация автора

У меня есть таблица аудита, поддерживающая статус пользователя, когда он меняется. Таблица похожа на приведенную ниже.

USER_ENTL_ID    USER_STATUS CREATED_Date
1                  S         10/20/2017
1                  C         10/21/2017
1                  W         10/22/2017
1                  SP        10/23/2017
2                  S         10/24/2017
2                  C         10/25/2017

Каждый пользователь может не переходить в каждое состояние приложения, и это нормально. то, что я хочу сделать, это захватить дату и статус для каждого пользователя, когда он изменился, и поместить его в одну строку.

Поэтому на выходе я ожидаю что-то вроде ниже.

id  S_status    s_created   c_status    c_created   W_status    w_created   sp_STATUS   SP_CREATED
1   S           10/20/2017    C         10/21/2017   W          10/22/2017      SP      10/23/2017
2   S           10/24/2017    C         10/25/2017              

Я читал о pivot, unpivot и decode, но я не уверен, что это вообще возможно в oracle. и если да, может кто-нибудь направить меня на правильный путь?

Автор: user641887 Источник Размещён: 08.11.2017 11:59

Ответы (3)


1 плюс

37789 Репутация автора

Что-то вроде следующего может быть легче понять:

WITH cteID AS (SELECT DISTINCT USER_ENTL_ID AS ID FROM AUDIT_TABLE),
     cteS  AS (SELECT USER_ENTL_ID AS ID,
                      'S' AS S_STATUS,
                      MIN(CREATED_DATE) AS S_CREATED
                 FROM AUDIT_TABLE
                 WHERE STATUS = 'S'
                 GROUP BY USER_ENTL_ID),
     cteC AS  (SELECT USER_ENTL_ID AS ID,
                      'C' AS C_STATUS,
                      MIN(CREATED_DATE) AS C_CREATED
                 FROM AUDIT_TABLE
                 WHERE STATUS = 'C'
                 GROUP BY USER_ENTL_ID),
     cteSP AS (SELECT USER_ENTL_ID AS ID,
                      'SP' AS SP_STATUS,
                      MIN(CREATED_DATE) AS SP_CREATED
                 FROM AUDIT_TABLE
                 WHERE STATUS = 'SP'
                 GROUP BY USER_ENTL_ID),
     cteW AS  (SELECT USER_ENTL_ID AS ID,
                      'W' AS W_STATUS,
                      MIN(CREATED_DATE) AS W_CREATED
                 FROM AUDIT_TABLE
                 WHERE STATUS = 'W'
                 GROUP BY USER_ENTL_ID)
SELECT i.ID,
       s.S_STATUS,
       s.S_CREATED,
       c.C_STATUS,
       c.C_CREATED,
       sp.SP_STATUS,
       sp.SP_CREATED,
       w.W_STATUS,
       w.W_CREATED
  FROM cteID i
  LEFT OUTER JOIN cteS s
    ON s.ID = i.ID
  LEFT OUTER JOIN cteC c
    ON c.ID = i.ID
  LEFT OUTER JOIN cteSP sp
    ON sp.ID = i.ID
  LEFT OUTER JOIN cteW w
    ON w.ID = i.ID

Удачи.

Автор: Bob Jarvis Размещён: 09.11.2017 12:17

1 плюс

23581 Репутация автора

Решение

Использование «условных агрегатов» является традиционным и все еще эффективным способом удовлетворения таких потребностей:

SQL Fiddle

Настройка схемы PostgreSQL 9.6 :

CREATE TABLE AUDIT_TABLE
    (USER_ENTL_ID int, USER_STATUS varchar(2), CREATED_DATE timestamp)
;

INSERT INTO AUDIT_TABLE
    (USER_ENTL_ID, USER_STATUS, CREATED_DATE)
VALUES
    (1, 'S', '2017-10-20 00:00:00'),
    (1, 'C', '2017-10-21 00:00:00'),
    (1, 'W', '2017-10-22 00:00:00'),
    (1, 'SP', '2017-10-23 00:00:00'),
    (2, 'S', '2017-10-24 00:00:00'),
    (2, 'C', '2017-10-25 00:00:00')
;

Запрос 1 :

Примечание: использование MIN или MAX в этом случае может иметь значение в зависимости от ваших данных, но если в данных на каждое выходное местоположение имеется только одно значение, то можно использовать любую функцию.

SELECT
      USER_ENTL_ID
    , MAX(CASE WHEN USER_STATUS = 'S' THEN USER_STATUS END) s_status
    , MIN(CASE WHEN USER_STATUS = 'S' THEN CREATED_DATE END) s_created
    , MAX(CASE WHEN USER_STATUS = 'C' THEN USER_STATUS END) c_status
    , MIN(CASE WHEN USER_STATUS = 'C' THEN CREATED_DATE END) c_created
    , MAX(CASE WHEN USER_STATUS = 'W' THEN USER_STATUS END) w_status
    , MIN(CASE WHEN USER_STATUS = 'W' THEN CREATED_DATE END) w_created
    , MAX(CASE WHEN USER_STATUS = 'SP' THEN USER_STATUS END) sp_status
    , MIN(CASE WHEN USER_STATUS = 'SP' THEN CREATED_DATE END) sp_created
FROM AUDIT_TABLE
GROUP BY 
      USER_ENTL_ID

Результаты :

| user_entl_id | s_status |            s_created | c_status |            c_created | w_status |            w_created | sp_status |           sp_created |
|--------------|----------|----------------------|----------|----------------------|----------|----------------------|-----------|----------------------|
|            1 |        S | 2017-10-20T00:00:00Z |        C | 2017-10-21T00:00:00Z |        W | 2017-10-22T00:00:00Z |        SP | 2017-10-23T00:00:00Z |
|            2 |        S | 2017-10-24T00:00:00Z |        C | 2017-10-25T00:00:00Z |   (null) |               (null) |    (null) |               (null) |

ADDED

Для дальнейшего объяснения: если вы удалите функции MIN или MAX, а также удалите группу, вот что вы получите:

+--------------+----------+----------------------+----------+----------------------+----------+----------------------+-----------+----------------------+
| user_entl_id | s_status |      s_created       | c_status |      c_created       | w_status |      w_created       | sp_status |      sp_created      |
+--------------+----------+----------------------+----------+----------------------+----------+----------------------+-----------+----------------------+
|            1 | S        | 2017-10-20T00:00:00Z | (null)   | (null)               | (null)   | (null)               | (null)    | (null)               |
|            1 | (null)   | (null)               | C        | 2017-10-21T00:00:00Z | (null)   | (null)               | (null)    | (null)               |
|            1 | (null)   | (null)               | (null)   | (null)               | W        | 2017-10-22T00:00:00Z | (null)    | (null)               |
|            1 | (null)   | (null)               | (null)   | (null)               | (null)   | (null)               | SP        | 2017-10-23T00:00:00Z |
|            2 | S        | 2017-10-24T00:00:00Z | (null)   | (null)               | (null)   | (null)               | (null)    | (null)               |
|            2 | (null)   | (null)               | C        | 2017-10-25T00:00:00Z | (null)   | (null)               | (null)    | (null)               |
+--------------+----------+----------------------+----------+----------------------+----------+----------------------+-----------+----------------------+

Если вы изучите это, вы увидите, что для данных, которые нас интересуют, в каждой строке есть только одно значение ( для каждого USER_ENTL_ID ), но они распределены по нескольким строкам. Таким образом, функции MIN / MAX и GROUP BY «сглаживают» результат, поэтому мы получаем желаемый результат. QED

Автор: Used_By_Already Размещён: 09.11.2017 12:50

1 плюс

59513 Репутация автора

Просто используйте PIVOT:

SQL Fiddle

Настройка схемы Oracle 11g R2 :

CREATE TABLE AUDIT_TABLE (USER_ENTL_ID, USER_STATUS, CREATED_DATE) AS
SELECT 1, 'S',  TIMESTAMP '2017-10-20 00:00:00' FROM DUAL UNION ALL
SELECT 1, 'C',  TIMESTAMP '2017-10-21 00:00:00' FROM DUAL UNION ALL
SELECT 1, 'W',  TIMESTAMP '2017-10-22 00:00:00' FROM DUAL UNION ALL
SELECT 1, 'SP', TIMESTAMP '2017-10-23 00:00:00' FROM DUAL UNION ALL
SELECT 2, 'S',  TIMESTAMP '2017-10-24 00:00:00' FROM DUAL UNION ALL
SELECT 2, 'C',  TIMESTAMP '2017-10-25 00:00:00' FROM DUAL

Запрос 1 :

SELECT *
FROM   AUDIT_TABLE
PIVOT (
  MAX( Created_Date ) AS Created,
  MAX( User_Status ) AS  Status
  FOR User_Status IN (
    'S' AS S, 'C' AS C, 'W' AS W, 'SP' AS SP
  )
)

Результаты :

| USER_ENTL_ID |             S_CREATED | S_STATUS |             C_CREATED | C_STATUS |             W_CREATED | W_STATUS |            SP_CREATED | SP_STATUS |
|--------------|-----------------------|----------|-----------------------|----------|-----------------------|----------|-----------------------|-----------|
|            1 | 2017-10-20 00:00:00.0 |        S | 2017-10-21 00:00:00.0 |        C | 2017-10-22 00:00:00.0 |        W | 2017-10-23 00:00:00.0 |        SP |
|            2 | 2017-10-24 00:00:00.0 |        S | 2017-10-25 00:00:00.0 |        C |                (null) |   (null) |                (null) |    (null) |
Автор: MT0 Размещён: 09.11.2017 02:06
Вопросы из категории :
32x32