SQL Conditional SELECT с COALESCE (возможная проблема с группировкой или объединением таблиц)

Заранее извиняюсь за длину вопроса:

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

Мы храним множество статистических данных по отдельным линиям продуктов, и каждое предприятие хотело бы иметь возможность просматривать данные на уровне линии; однако некоторые предприятия также организуют свои продукты в группы по отделам, а затем даже группируют отделы по «потоку создания ценности» (просто еще один уровень в иерархии).

Что я хотел бы сделать, так это отображать данные в самой высокой доступной группе (объекты с потоками создания ценности получают список потоков для объекта, объекты, у которых есть только линейки продуктов, просматривают список продуктов и т. д.).

Я использовал функцию COALESCE, чтобы помочь с этим, так как я заинтересован в проверке значений NULL, чтобы определить, какую организационную группу использует учреждение.

К сожалению, необходимые данные находятся в 6 разных таблицах, что требует нескольких объединений. Я добавил дополнительные параметры для каждого соединения, чтобы попытаться убедиться, что соединения корректны.

Запрос правильно работает со средством, которое не использует потоки или отделы (1 в приведенном ниже примере); генерируется список продуктов и их соответствующие баллы.

Запрос не дает желаемых результатов для других уровней. Для предприятия, организованного с помощью потоков создания ценности (2), желаемым результатом является список потоков этого предприятия и их соответствующие оценки. Вместо этого снова создается список продуктовых линеек.

Вот мой запрос:

select * from
(select a.*, rownum rnum from (

SELECT /*STREAM_ID, DEPT_ID ,LINE,*/ 
  COALESCE(VS.ID, DT.ID, SLR.LINE) "ID", 
  COALESCE(VS.NAME, DT.NAME, SLR.NAME) "NAME", 
  case when SAFETY_VALUE = 0 then 'GREEN' when SAFETY_VALUE > 0 then 'RED' else 'WHITE' end AS COLOR 
FROM XX_SQDC_LINES_REF SLR  
LEFT JOIN SQDC_DEPARTMENT_DETAILS DTD on SLR.SITE=DTD.FACILITY_ID AND SLR.LINE=DTD.LINE_ID
LEFT JOIN SQDC_VALUE_STREAM_DETAILS VSD ON DTD.FACILITY_ID= VSD.FACILITY_ID AND DTD.LINE_ID=VSD.LINE_ID
LEFT JOIN SQDC_SAFETY_MAX KPI ON VSD.FACILITY_ID=KPI.FACILITY_ID AND VSD.LINE_ID=KPI.LINE_ID
LEFT JOIN SQDC_DEPARTMENTS DT ON KPI.FACILITY_ID= DT.FACILITY_ID and dtd.dept_id=dt.id
LEFT JOIN SQDC_VALUE_STREAMS VS ON DT.FACILITY_ID= VS.FACILITY_ID AND DT.VS_ID=VS.ID
WHERE (SITE = 2)
ORDER by NAME

)a)

Таблицы (примеры значений; есть также дополнительные столбцы, которые я пропустил, поскольку они не относятся к этому запросу):

ХХ_SQDC_LINES_REF

    Line Name    Site
    1    Table   1
    2    Lamp    1
    3    Screen  2
    4    Forcep  2
    5    Brush   2
    6    Camera2 2
    7    Screen2 2
    8    Forcep2 2
    9    Brush2  2
    10   Camera2 2

SQDC_DEPARTMENT_DETAILS

    Dept_ID Line_ID Facility_ID
    1       3       2
    1       4       2
    2       5       2 
    2       6       2
    3       7       2 
    3       8       2
    4       9       2 
    4       10      2

SQDC_VALUE_STREAM_DETAILS

    Stream_ID Line_ID Facility_ID
    1         3       2
    1         4       2
    1         5       2 
    1         6       2
    2         7       2 
    2         8       2
    2         9       2 
    2         10      2

SQDC_SAFETY_MAX

    Facility_ID Line_ID Actual_Date Safety_Value
    1           1       31-Jan-16   0
    1           2       31-Jan-16   0
    2           3       31-Jan-16   0
    2           4       24-Jan-16   10
    2           5       24-Jan-16   0
    2           7       24-Jan-16   0
    2           9       24-Jan-16   0

SQDC_DEPARTMENTS

    ID Name   Facility_ID VS_ID
    1  Dept 1 2           1
    2  Dept 2 2           1
    3  Dept 3 2           2
    4  Dept 4 2           2

SQDC_VALUE_STREAMS

    ID Name   Facility_ID
    1  VS 1   2
    2  VS 2   2

Заранее спасибо всем, кто может помочь указать мне в правильном направлении.

Отредактировано, чтобы добавить ожидаемые результаты:

Сайт 1 (без трансляции):

    ID Name  Color RNUM
    1  Table GREEN 1
    2  Lamp  GREEN 2

Сайт 2 (с потоком):

    ID Name Color RNUM
    1  VS 1 GREEN 1
    2  VS 2 WHITE 2

Я знаю, что мне не хватает некоторых дополнительных функций или операторов, чтобы все работало так, как мне бы хотелось, поэтому давайте посмотрим, смогу ли я перефразировать ожидания:

Я хотел бы отобразить идентификатор, имя и цветовой код значения безопасности для самого высокого уровня (потока, отдела или продукта), используемого на объекте, и в последний день была предоставлена ​​​​информация.

Согласно таблицам VS и Dept, на сайте 1 нет потоков или отделов, поэтому список продуктов должен быть заполнен. С предоставленными данными сайт 1 будет отображать оба продукта по отдельности с ЗЕЛЕНЫМИ значениями, поскольку данные обоих продуктов относятся к 31 января 2016 года, а значения равны 0. Если бы на сайте 1 был другой продукт с данными до этой даты, цвет был бы быть БЕЛЫМ независимо от фактического значения безопасности.

В идеале сайт 2 должен отображать список только двух потоков создания ценности (поскольку это самый высокий уровень).

Последняя дата для Зоны 2 — 31 января 16 года. В линейках продуктов под VS 1 только Line_ID=3 имеет эту дату (остальные строки имеют более ранние даты). Таким образом, среднее значение безопасности будет равно 0 (10 для Line_ID=4 не будет частью среднего значения из-за более ранней даты), и тогда цвет будет ЗЕЛЕНЫМ.

Все значения безопасности для продуктовых линеек в рамках VS 2 были получены до 31 января 2016 г. и, следовательно, не рассчитывались в среднем, поэтому результат белый.

Надеюсь, это поможет прояснить ситуацию.

Запросы одного случая на запрос от @Hogan:

Запрос на уровне потока создания ценности (например, сайт 2):

    select *
          from
        (select a.*, rownum rnum
           from
           (SELECT stream_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN'  when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM SQDC_VALUE_STREAMS VLS  LEFT JOIN SQDC_VALUE_STREAM_DETAILS VS ON VLS.ID = VS.STREAM_ID LEFT JOIN SQDC_SAFETY_MAX KPI ON VS.LINE_ID=KPI.LINE_ID 

    WHERE (vs.facility_id = 2
    )
    group by name, STREAM_id
    ORDER by NAME) a
    )

Запрос на уровне отдела (в примере такого сайта нет, но он будет работать так же):

    select *
          from
        (select a.*, rownum rnum
           from
           (SELECT dept_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN'  when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM SQDC_DEPARTMENTS DPTS  LEFT JOIN SQDC_DEPARTMENT_DETAILS DT ON DPTS.ID = DT.Dept_ID LEFT JOIN SQDC_SAFETY_MAX KPI ON DT.LINE_ID=KPI.LINE_ID

    WHERE (DT.facility_id = 7986128121911792
    )
    group by name, DEPT_id
    ORDER by NAME) a
    )

Запрос уровня продукта (например, сайт 1):

    select *
          from
        (select a.*, rownum rnum
           from
           (SELECT line, NAME,  case when SAFETY_VALUE = 0 then 'GREEN'  when      SAFETY_VALUE > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM XX_SQDC_LINES_REF SLR  LEFT JOIN SQDC_SAFETY_MAX KPI ON     SLR.LINE=KPI.LINE_ID 

    WHERE (SITE = 1
    )
    ORDER by NAME) a
    )

Цель состоит в том, чтобы объединить эти три запроса в один и обеспечить отображение только самого высокого организационного уровня (поток, отдел, продукт) для объекта.


sql oracle join coalesce
person Simon.626    schedule 28.01.2016    source источник
comment
Разве вы не можете добавить ожидаемый результат (с данными этой таблицы)?   -  person jarlh    schedule 28.01.2016
comment
Для таких сложных проблем, как эта, я считаю полезным протестировать, создав несколько промежуточных представлений/материализованных представлений/глобальных временных таблиц, чтобы вы могли проверить шаги, чтобы получить окончательный ответ.   -  person kevinskio    schedule 28.01.2016
comment
Ваши результаты не соответствуют вашему запросу — в ваш запрос вы включаете столбец с именем rownum — почему этого нет в ваших результатах?   -  person Hogan    schedule 28.01.2016
comment
Ты прав; Я забыл войти в этот ряд. Отредактировано.   -  person Simon.626    schedule 28.01.2016

Ответы (2)


arrow_upward
1
arrow_downward

Учитывая ваши 3 запроса, их нетрудно объединить с описанными вами правилами.

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

WITH stream_query AS
(
  SELECT 1 AS PRIORITY, stream_id, null as dept_id, null as line, NAME, COLOR, rownum rnum
  FROM (
    SELECT stream_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN'  when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR
    FROM SQDC_VALUE_STREAMS VLS
    LEFT JOIN SQDC_VALUE_STREAM_DETAILS VS ON VLS.ID = VS.STREAM_ID
    LEFT JOIN SQDC_SAFETY_MAX KPI ON VS.LINE_ID=KPI.LINE_ID 
    WHERE vs.facility_id = 2
    group by name, STREAM_id
    ORDER by NAME
  ) a
), dept_query AS
(
  SELECT 2 AS PRIORITY, null as stream_id, dept_id, null as line, NAME, COLOR, rownum rnum 
  FROM (
    SELECT dept_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN'  when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM SQDC_DEPARTMENTS DPTS  
    LEFT JOIN SQDC_DEPARTMENT_DETAILS DT ON DPTS.ID = DT.Dept_ID 
    LEFT JOIN SQDC_SAFETY_MAX KPI ON DT.LINE_ID=KPI.LINE_ID
    WHERE DT.facility_id = 7986128121911792
    group by name, DEPT_id
    ORDER by NAME
  ) a
), prod_query AS
(
  SELECT 3 AS PRIORITY, null as stream_id, null as dept_id, line, NAME, COLOR, rownum rnum
  FROM (
    SELECT line, NAME,  case when SAFETY_VALUE = 0 then 'GREEN'  when      SAFETY_VALUE > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM XX_SQDC_LINES_REF SLR 
    LEFT JOIN SQDC_SAFETY_MAX KPI ON     SLR.LINE=KPI.LINE_ID 
    WHERE SITE = 1
    ORDER by NAME
  ) a
), merged AS
(
  SELECT a.*, MIN(PRIORITY) OVER () AS HIGHEST 
  FROM (
    SELECT * FROM stream_query
      UNION ALL
    SELECT * FROM dept_query
      UNION ALL
    SELECT * FROM prod_query
  ) a  
)
SELECT *
FROM merged
WHERE PRIORITY = HIGHEST

Ваша идея была найти, но вы неправильно настроили соединения. Вам нужно было выйти из соединения с XX_SQDC_LINES_REF или SQDC_DEPARTMENT_DETAILS — вы этого не делали, вы присоединялись к предыдущему соединению

SELECT /*STREAM_ID, DEPT_ID ,LINE,*/ 
  COALESCE(VS.ID, DT.ID, SLR.LINE) "ID", 
  COALESCE(VS.NAME, DT.NAME, SLR.NAME) "NAME", 
  case when SAFETY_VALUE = 0 then 'GREEN' when SAFETY_VALUE > 0 then 'RED' else 'WHITE' end AS COLOR,
  rownum rnum
FROM XX_SQDC_LINES_REF SLR  
LEFT JOIN SQDC_DEPARTMENT_DETAILS DTD   on SLR.SITE=DTD.FACILITY_ID AND SLR.LINE=DTD.LINE_ID
LEFT JOIN SQDC_VALUE_STREAM_DETAILS VSD ON SLR.SITE=VSD.FACILITY_ID AND SLR.LINE=VSD.LINE_ID
LEFT JOIN SQDC_SAFETY_MAX KPI           ON SLR.SITE=KPI.FACILITY_ID AND SLR.LINE=KPI.LINE_ID
LEFT JOIN SQDC_DEPARTMENTS DT           ON SLR.SITE=DT.FACILITY_ID and  DTD.dept_id=DT.id
LEFT JOIN SQDC_VALUE_STREAMS VS         ON SLR.SITE=VS.FACILITY_ID AND  DTD.VS_ID=VS.ID
WHERE (SITE = 2)
ORDER by NAME

Я также удалил все подзапросы, поскольку они не нужны и только усложняют (IMO)

person Hogan    schedule 28.01.2016
comment
Это оставляет проблему с последним соединением для SQDC_VALUE_STREAMS. SQDC_DEPARTMENT_DETAILS не имеет эквивалента переменной VS_ID. Этот столбец находится в таблицах SQDC_DEPARTMENTS и SQDC_VALUE_STREAM_DETAILS, но присоединение к этим таблицам также не помогает. - person Simon.626; 28.01.2016
comment
@ Simon.626 -- я не уверен... Мне нужно увидеть лучшую спецификацию --- мне непонятно, каков ваш ожидаемый результат. Какой вход дает два разных выхода? это SITE = 1 против SITE = 2? - person Hogan; 28.01.2016
comment
Правильный. Поскольку сайту 1 не назначены значения потоков или отделов, функции объединения должны выбирать ID и NAME из XX_SQDC_LINES_REF; на сайте 2 есть потоки создания ценности, поэтому ID и NAME должны быть из SQDC_VALUE_STREAM_DETAILS и SQDC_VALUE_STREAMS соответственно. - person Simon.626; 28.01.2016
comment
Откуда мы знаем, что на площадке 2 есть потоки создания ценности? - person Hogan; 28.01.2016
comment
по столбцу Facility_ID в таблицах SQDC_VALUE_STREAMS и SQDC_VALUE_STREAM_DETAILS мы видим, что единственным сайтом с потоками создания ценности является 2. - person Simon.626; 28.01.2016
comment
Я не понимаю, почему у вас есть только два результата в примере SITE = 2, когда в XX_SQDC_LINES_REF есть 7 строк с SITE = 2. - person Hogan; 28.01.2016
comment
См. конец моего вопроса для последнего редактирования. Это, надеюсь, сделает это менее запутанным. - person Simon.626; 28.01.2016
comment
@ Саймон.626 - Это не имело смысла. - person Hogan; 28.01.2016
comment
Я боялся, что так и будет. Какую часть я могу разъяснить? Я действительно ценю, сколько внимания вы уделили этому, кстати. - person Simon.626; 28.01.2016
comment
Может быть, было бы проще, если бы вы написали оператор SQL, который давал бы правильные результаты для SITE = 1, а затем еще один, который давал бы правильные результаты для SITE = 2, и еще один для любого другого варианта использования. Если я увижу правильный SQL для одного случая, я могу показать вам, как их комбинировать. - person Hogan; 28.01.2016
comment
Хорошая идея; Собственно, с этого я и начал, поэтому я смог поместить их в конец исходного вопроса. - person Simon.626; 29.01.2016
comment
@ Simon.626 - я разместил запрос, который показывает, как объединить эти 3 запроса. - person Hogan; 29.01.2016
comment
Этот запрос, кажется, не работает для меня. Когда я использую сайт 1, я не получаю никаких результатов; когда я использую Сайт 2, он правильно отображает только потоки создания ценности и их цвет, но каждую запись отображает дважды. Кроме того (не уверен, что я сделал это ясно ранее), но когда я запускаю весь запрос, vs.facility_id=DT.facility_id=SITE. - person Simon.626; 29.01.2016
comment
@ Simon.626 - да, как написано, вы должны отредактировать предложение where, чтобы оно было одинаковым в каждом CTE. Я бы изменил их, чтобы использовать ту же переменную, если бы это не был тестовый код. - person Hogan; 29.01.2016
comment
Я изменил предложения where, когда запускал его; Я не был уверен, что это было принято во внимание. Я все еще получаю неверный вывод, когда запускаю его, как описано в моем предыдущем комментарии. - person Simon.626; 29.01.2016
comment
@Simon.626 мой полный тестовый пример находится по адресу gist.github.com/hoganlong/9ba160dd1e11de8fe9e3 - - для DB2 (поскольку это то, что у меня есть) он отлично работает и должен работать и в оракуле - просто удален rownum. У меня была опечатка в объединенном CTE, которая могла быть вашей проблемой. Теперь это исправлено. - person Hogan; 29.01.2016

arrow_upward
0
arrow_downward

С большой помощью @Hogan вот готовый запрос:

SELECT COALESCE(STREAM_ID, DEPT_ID, TO_CHAR(LINE)) ID, NAME,COLOR
FROM(
WITH  stream_query AS
(
  SELECT 1 AS PRIORITY, stream_id, null as dept_id, null as line, NAME, COLOR, rownum rnum
  FROM (
    SELECT stream_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN' when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR
    FROM SQDC_VALUE_STREAMS VLS
    LEFT JOIN SQDC_VALUE_STREAM_DETAILS VS ON VLS.ID = VS.STREAM_ID
    LEFT JOIN (select line_id, safety_value, actual_date from SQDC_SAFETY_MAX where actual_date in (select max(actual_date) from SQDC_SAFETY_MAX)) KPI ON VS.LINE_ID=KPI.LINE_ID 
    WHERE vs.facility_id = [replace1]
    group by name, STREAM_id
    ORDER by NAME
  ) a
), dept_query AS
(
  SELECT 2 AS PRIORITY, null as stream_id, dept_id, null as line, NAME, COLOR, rownum rnum 
  FROM (
    SELECT dept_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN'  when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM SQDC_DEPARTMENTS DPTS  
    LEFT JOIN SQDC_DEPARTMENT_DETAILS DT ON DPTS.ID = DT.Dept_ID 
    LEFT JOIN (select line_id, safety_value, actual_date from SQDC_SAFETY_MAX where actual_date in (select max(actual_date) from SQDC_SAFETY_MAX)) KPI ON DT.LINE_ID=KPI.LINE_ID
    WHERE DT.facility_id = [replace1]
    group by name, DEPT_id
    ORDER by NAME
  ) a
), prod_query AS
(
  SELECT 3 AS PRIORITY, null as stream_id, null as dept_id, line, NAME, COLOR, rownum rnum
  FROM (
    SELECT line, NAME,  case when SAFETY_VALUE = 0 then 'GREEN'  when      SAFETY_VALUE > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM XX_SQDC_LINES_REF SLR 
    LEFT JOIN SQDC_SAFETY_MAX KPI ON     SLR.LINE=KPI.LINE_ID 
    WHERE SITE =[replace1]
    ORDER by NAME
  ) a
),merged AS
(
  SELECT a.*, MIN(PRIORITY) OVER () AS HIGHEST 
  FROM (
    SELECT * FROM stream_query
      UNION ALL
    SELECT * FROM dept_query
      UNION ALL
    SELECT * FROM prod_query
  ) a  
)
SELECT *
FROM merged
WHERE PRIORITY = HIGHEST
)
where rownum <=12
AND rnum >=1

Я добавил немного больше, чтобы выбрать последнюю доступную дату и ограничить количество строк и столбцов, но это работает как для тестовых случаев, так и для моих реальных данных. Еще раз спасибо, @Hogan.

person Simon.626    schedule 29.01.2016