Как определить, изменилось ли значение столбца взад и вперед в течение диапазона дат?

Question

Фон:

У нас есть рабочий запрос для выбора типа доступа (d8_5_B.xpostaccessuse) пациент (колонка xpid в любой таблице) используется 90 дней после даты, когда они стали хроническими (d1.ddiadate +'90 days'). Выходные данные состоят из двух столбцов: одна дата, одно целое число.

Запрос:

SELECT
    d8_B.start_date,
    d8_5_B.xpostaccessuse
FROM d1 
    LEFT JOIN d8 d8_B 
        ON d1.xpid = d8_B.xpid
    LEFT JOIN d8_5 d8_5_B 
        ON d8_B.xsession_id = d8_5_B.xsession
WHERE
    d8_B.start_date IN (
        SELECT MAX(d8.start_date)
        FROM d8
            LEFT JOIN d1 
                ON d8.xpid = d1.xpid 
        WHERE d8.start_date <= d1.ddiadate + '90 days'
        GROUP BY d8.xpid
    )

Цель:

Мы должны иметь возможность определить, изменился ли их доступ туда и обратно в течение этого 90-дневного периода. В идеале мы хотели бы также иметь возможность выделить то, на что оно переключилось и когда, но меньшие победы все еще полезны.

• Мы знаем d8_5_B.xpostaccessuse = 3 на d1.ddiadate,
• Мы знаем d8_5_B.xpostaccessuse = 3 на d1.ddiadate +'90 days',

Но если их доступ изменился с 3 сказать 5 и обратно к 3 снова в течение этого 90-дневного диапазона, мы не можем сказать, что это не было 3 все это время.

Как мы можем определить:

• если значение изменилось с 3 к чему-то еще и обратно к 3в течение этих 90 дней?
• во что это изменилось и когда?

Работа до сих пор:

Наши лучшие мысли на данный момент связаны с использованием count() агрегат, чтобы подсчитать общее количество строк в диапазоне дат, а затем подсчитать строки, где d8_5_B.xpostaccessuse = 3 и сравните результаты. Это скажет нам, если есть разрыв, даже если не то, что это такое. Мы не смогли получить такой подход к работе, в первую очередь из-за необходимых group by,

Другая идея, которую мы имели, пыталась выбрать различные значения d8_5_B.xpostaccessuse а также group by xpid, но та же проблема возникает с group by,

---

Обновления:

• Энгр 9
• Открыты для предложений для части "что и когда". Я представлял, возможно, столбец для каждого, на что он изменился в середине и в какую дату это изменение началось. Это очевидно становится сложным, если пациент несколько раз менял доступ, но это не должно происходить часто. На данный момент сообщение о дополнительной строке было бы приемлемо, если кто-то не думает о более элегантном решении.

• Пример таблицы (кредит Джонс Джонс ниже):

  create table d1
  (xpid integer, ddiadate ingresdate);
  
  create table d8
  (xpid integer, xsession_id integer, start_date ingresdate);
  
  create table d8_5
  (xpid integer, xsession integer, xpostaccessuse integer);
  
  insert into d1 values(1, '2018-01-01');
  insert into d1 values(2, '2018-01-01');
  
  insert into d8 values(1, 1, '2018-01-01');
  insert into d8 values(1, 2, '2018-01-10');
  insert into d8 values(1, 3, '2018-01-20');
  
  insert into d8 values(2, 1, '2018-01-01');
  insert into d8 values(2, 2, '2018-01-10');
  insert into d8 values(2, 3, '2018-01-20');
  
  insert into d8_5 values(1, 1, 3);
  insert into d8_5 values(1, 2, 3);
  insert into d8_5 values(1, 3, 3);
  
  insert into d8_5 values(2, 1, 3);
  insert into d8_5 values(2, 2, 5);
  insert into d8_5 values(2, 3, 3);
  

• Табличные определения, как они относятся к этой задаче:

  d1: таблица демографии / базовая информация о пациенте

  • Идентификатор пациента и индекс таблицы (d1.xpid)
  • Дата Первая Хроника (d1.ddiadate)

  d8: сессионный стол

  • ID сеанса и индекс таблицы (d8.xsession_id)
  • ID пациента (d8.xpid)
  • Дата начала сеансаd8.start_date)

  d8_5: подтаблица сеанса с данными доступа

  • ID сеанса и индекс таблицы (d8_5.xsession)
  • ID пациента (d8_5.xpid)
  • Тип доступа (d8_5.xpostaccessuse)

  Обратите внимание, что все три таблицы могут быть объединены на xpid а также d8.xsession_id = d8_5.xsession

Answer 1

Вы, вероятно, после некоторого select COUNT(DISTINCT d8_5_B.xpostaccessuse) используя group by чтобы показать вам, сколько у пациента было состояния.

Предоставьте data-test и create table, если вы хотите полный запрос;)

---

Редактировать:

Это, вероятно, покажет вам количество различных обращений за 90 дней (я использовал INNER JOINвроде как левый твой бесполезен)

SELECT
    d8.start_date,
    d5.xpostaccessuse,
    d1.access_count
FROM (
    SELECT 
        d8.xpid, 
        MAX(d8.start_date) date_max, 
        COUNT(DISTINCT d5.xpostaccessuse) access_count
    FROM d8
    INNER JOIN d1 
        ON d8.xpid = d1.xpid 
    INNER JOIN d8_5 d5 
        ON d8.xsession_id = d5.xsession
    WHERE 
        d8.start_date > d1.ddiadate 
        AND d8.start_date <= d1.ddiadate + '90 days'
    GROUP BY d8.xpid
) d1 
INNER JOIN d8 
    ON d1.xpid = d8.xpid
    AND d8.start_date = d1.date_max
INNER JOIN d8_5 d5 
    ON d8.xsession_id = d5.xsession

Answer 2

Поскольку Ingres упоминается здесь, одним из возможных подходов может быть использование процедуры базы данных, производящей строки, чтобы определить, какие строки не меняются, и избежать возврата тех, которые вас не интересуют. Ниже приведен пример, основанный на моей интерпретации таблиц. похоже, надеюсь, что это будет полезно.

-- Some sample data.

create table d1
(xpid integer, ddiadate ingresdate);

create table d8
(xpid integer, xsession_id integer, start_date ingresdate);

create table d8_5
(xpid integer, xsession integer, xpostaccessuse integer);

insert into d1 values(1, '2018-01-01');
insert into d1 values(2, '2018-01-01');

insert into d8 values(1, 1, '2018-01-01');
insert into d8 values(1, 2, '2018-01-10');
insert into d8 values(1, 3, '2018-01-20');

insert into d8 values(2, 1, '2018-01-01');
insert into d8 values(2, 2, '2018-01-10');
insert into d8 values(2, 3, '2018-01-20');

insert into d8_5 values(1, 1, 3);
insert into d8_5 values(1, 2, 3);
insert into d8_5 values(1, 3, 3);

insert into d8_5 values(2, 1, 3);
insert into d8_5 values(2, 2, 5);
insert into d8_5 values(2, 3, 3);

-- Query to show patient's access type at each session
-- during the 3 months before becoming chronic.

select d1.xpid, d8.start_date, d8_5.xpostaccessuse
from d1
join d8 on d1.xpid = d8.xpid
join d8_5 on d1.xpid = d8_5.xpid and d8.xsession_id = d8_5.xsession
where d8.start_date <= d1.ddiadate + '90 days'
and d8.start_date >= d1.ddiadate
order by xpid, start_date;

-- Use a row-producing database procedure, based on the
-- above query, to return a row only when data changes.

create procedure rpp
result row r (xpid integer, start_date date, xpostaccessuse integer) =
declare
  xpid = integer;
  sd = ingresdate;
  xp = integer;
  xplast = integer;
  xpidlast = integer;
begin
  xplast = -1; xpidlast = -1;
  for
  select d1.xpid, d8.start_date, d8_5.xpostaccessuse
  into :xpid, :sd, :xp
  from d1
  join d8 on d1.xpid = d8.xpid
  join d8_5 on d1.xpid = d8_5.xpid and d8.xsession_id = d8_5.xsession
  where d8.start_date <= d1.ddiadate + '90 days'
  and d8.start_date >= d1.ddiadate
  order by xpid, d8.start_date desc
  do
    if :xplast != :xp or :xpidlast != :xpid
    then
      return row(:xpid, :sd, :xp);
      xplast = :xp;
      xpidlast = :xpid;
    endif;
  endfor;
end;

-- Query the row-producing procedure.

select * from rpp() order by xpid, start_date;

Answer 3

В этом варианте SQL, Ingres 9 (довольно устаревший и, возможно, не имеющий отношения к остальной части Интернета, но в случае, если это не так...), существует множество ограничений на то, где вы можете использовать подзапросы.

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

Удачи!