SQL: выберите транзакции, в которых строки не соответствуют критериям внутри одной таблицы.

У меня есть таблица с транзакциями:

Transactions
------------
id | account | type | date_time             | amount
----------------------------------------------------
 1 | 001     | 'R'  | '2012-01-01 10:01:00' | 1000
 2 | 003     | 'R'  | '2012-01-02 12:53:10' | 1500
 3 | 003     | 'A'  | '2012-01-03 13:10:01' | -1500
 4 | 002     | 'R'  | '2012-01-03 17:56:00' | 2000
 5 | 001     | 'R'  | '2012-01-04 12:30:01' | 1000
 6 | 002     | 'A'  | '2012-01-04 13:23:01' | -2000
 7 | 003     | 'R'  | '2012-01-04 15:13:10' | 3000
 8 | 003     | 'R'  | '2012-01-05 12:12:00' | 1250
 9 | 003     | 'A'  | '2012-01-06 17:24:01' | -1250

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

Таким образом, запрос должен выдать следующие строки, учитывая предыдущий пример:

id | account |type  | date                  | amount
----------------------------------------------------
 1 | 001     | 'R'  | '2012-01-01 10:01:00' | 1000
 5 | 001     | 'R'  | '2012-01-04 12:30:01' | 1000
 7 | 003     | 'R'  | '2012-01-04 15:13:10' | 3000

(Как видите, строка 2 не отображается, потому что строка 3 «отменяет» ее... также строка 4 «отменяется» строкой 6'; появляется строка 7 (хотя учетная запись 003 принадлежит отмененной строке № 2). , на этот раз в строке 7 она не отменяется ни одной строкой «А»); И строка 8 не появится (это тоже для 003 счета, так как теперь эта отменяется на 9, что тоже не отменяет 7, просто предыдущий раз: 8...

Я пробовал соединения, подзапросы в предложениях Where, но я действительно не уверен, как мне сделать свой запрос...

Что я пробовал:

Попытка присоединяется:

   SELECT trans.type as type,
          trans.amount as amount,
          trans.date_time as dt,
          trans.account as acct,
     FROM Transactions trans
INNER JOIN ( SELECT t.type AS type, t.acct AS acct, t.date_time AS date_time
               FROM Transactions t
              WHERE t.date_time > trans.date_time
           ORDER BY t.date_time DESC
          ) AS nextTrans
       ON nextTrans.acct = trans.acct
    WHERE trans.type IN ('R')
      AND nextTrans.type NOT IN ('A')
 ORDER BY DATE(trans.date_time) ASC

Это вызывает ошибку, так как я не могу ввести внешние значения для JOIN в MySQL.

Попытка подзапроса, где:

   SELECT trans.type as type,
          trans.amount as amount,
          trans.date_time as dt,
          trans.account as acct,
     FROM Transactions trans
    WHERE trans.type IN ('R')
      AND trans.datetime <
          ( SELECT t.date_time AS date_time
               FROM Transactions t
              WHERE t.account = trans.account
           ORDER BY t.date_time DESC
          ) AS nextTrans
       ON nextTrans.acct = trans.acct

 ORDER BY DATE(trans.date_time) ASC

Это неправильно, я могу ввести внешние значения в WHERE в MySQL, но мне не удается найти способ правильно фильтровать то, что мне нужно...

ВАЖНОЕ РЕДАКТИРОВАТЬ:

Мне удалось добиться решения, но теперь оно нуждается в серьезной оптимизации. Вот:

SELECT *
  FROM (SELECT t1.*, tFlagged.id AS cancId, tFlagged.type AS cancFlag
          FROM transactions t1
     LEFT JOIN (SELECT t2.*
                  FROM transactions t2
              ORDER BY t2.date_time ASC ) tFlagged
            ON (t1.account=tFlagged.account
                  AND
                t1.date_time < tFlagged.date_time)
         WHERE t1.type = 'R'
      GROUP BY t1.id) tCanc
 WHERE tCanc.cancFlag IS NULL
    OR tCanc.cancFlag <> 'A'

Я присоединился к таблице сам с собой, просто учитывая ту же учетную запись и отличное время date_time. Присоединение идет по дате_времени. Группировка по идентификатору Мне удалось получить только первый результат соединения, который оказывается следующей транзакцией для той же учетной записи.

Затем при внешнем выборе я отфильтровываю те, у которых есть «A», поскольку это означает, что следующая транзакция была фактически отменой для нее. Другими словами, если следующей транзакции для того же аккаунта нет или если следующая транзакция является «R», то она не отменяется и должна отображаться в результате...

Я получил это:

+----+---------+------+---------------------+--------+--------+----------+
| id | account | type | date_time           | amount | cancId | cancFlag |
+----+---------+------+---------------------+--------+--------+----------+
|  1 | 001     |   R  | 2012-01-01 10:01:00 |   1000 |      5 | R        |
|  5 | 001     |   R  | 2012-01-04 12:30:01 |   1000 |   NULL | NULL     |
|  7 | 003     |   R  | 2012-01-04 15:13:10 |   3000 |      8 | R        |
+----+---------+------+---------------------+--------+--------+----------+

Он связывает каждую транзакцию со следующей по времени для той же учетной записи, а затем отфильтровывает те, которые были отменены... Успех!!

Как я уже сказал, проблема сейчас в оптимизации. В моих реальных данных много строк (как ожидается, таблица, содержащая транзакции во времени), и для таблицы из ~ 10 000 строк прямо сейчас я получил положительный результат с этим запросом за 1 минуту 44 секунды. Я полагаю, что дело в соединениях... (Для тех, кто знает протокол здесь, что мне делать? Запустить новый вопрос здесь и опубликовать его как решение этого? Или просто дождаться дополнительных ответов здесь?)


mysql sql subquery
person Javier Novoa C.    schedule 28.02.2012    source источник
comment
См.: meta.stackexchange.com/questions/2950/   -  person OMG Ponies    schedule 28.02.2012
comment
спасибо за отзыв... :Р   -  person Javier Novoa C.    schedule 28.02.2012
comment
столбец id всегда упорядочен по времени? если это так, вы можете присоединиться к id=id+1.   -  person andrew cooke    schedule 28.02.2012
comment
нет, извини @andrewcooke, это был просто вопрос упрощения примера, который я показал здесь... Я прокомментировал то же самое для ответа на gcbenison здесь...   -  person Javier Novoa C.    schedule 28.02.2012

Ответы (5)


arrow_upward
2
arrow_downward

Вот решение, основанное на вложенных подзапросах. Во-первых, я добавил несколько строк, чтобы поймать еще несколько случаев. Транзакция 10, например, не должна быть отменена транзакцией 12, потому что транзакция 11 находится между ними.

> select * from transactions order by date_time;
+----+---------+------+---------------------+--------+
| id | account | type | date_time           | amount |
+----+---------+------+---------------------+--------+
|  1 |       1 | R    | 2012-01-01 10:01:00 |   1000 |
|  2 |       3 | R    | 2012-01-02 12:53:10 |   1500 |
|  3 |       3 | A    | 2012-01-03 13:10:01 |  -1500 |
|  4 |       2 | R    | 2012-01-03 17:56:00 |   2000 |
|  5 |       1 | R    | 2012-01-04 12:30:01 |   1000 |
|  6 |       2 | A    | 2012-01-04 13:23:01 |  -2000 |
|  7 |       3 | R    | 2012-01-04 15:13:10 |   3000 |
|  8 |       3 | R    | 2012-01-05 12:12:00 |   1250 |
|  9 |       3 | A    | 2012-01-06 17:24:01 |  -1250 |
| 10 |       3 | R    | 2012-01-07 00:00:00 |   1250 |
| 11 |       3 | R    | 2012-01-07 05:00:00 |   4000 |
| 12 |       3 | A    | 2012-01-08 00:00:00 |  -1250 |
| 14 |       2 | R    | 2012-01-09 00:00:00 |   2000 |
| 13 |       3 | A    | 2012-01-10 00:00:00 |  -1500 |
| 15 |       2 | A    | 2012-01-11 04:00:00 |  -2000 |
| 16 |       2 | R    | 2012-01-12 00:00:00 |   5000 |
+----+---------+------+---------------------+--------+
16 rows in set (0.00 sec)

Во-первых, создайте запрос, чтобы получить для каждой транзакции «дату самой последней транзакции перед этой в той же учетной записи»:

SELECT t2.*,
       MAX(t1.date_time) AS prev_date
FROM transactions t1
JOIN transactions t2
ON (t1.account = t2.account
   AND t2.date_time > t1.date_time)
GROUP BY t2.account,t2.date_time
ORDER BY t2.date_time;

+----+---------+------+---------------------+--------+---------------------+
| id | account | type | date_time           | amount | prev_date           |
+----+---------+------+---------------------+--------+---------------------+
|  3 |       3 | A    | 2012-01-03 13:10:01 |  -1500 | 2012-01-02 12:53:10 |
|  5 |       1 | R    | 2012-01-04 12:30:01 |   1000 | 2012-01-01 10:01:00 |
|  6 |       2 | A    | 2012-01-04 13:23:01 |  -2000 | 2012-01-03 17:56:00 |
|  7 |       3 | R    | 2012-01-04 15:13:10 |   3000 | 2012-01-03 13:10:01 |
|  8 |       3 | R    | 2012-01-05 12:12:00 |   1250 | 2012-01-04 15:13:10 |
|  9 |       3 | A    | 2012-01-06 17:24:01 |  -1250 | 2012-01-05 12:12:00 |
| 10 |       3 | R    | 2012-01-07 00:00:00 |   1250 | 2012-01-06 17:24:01 |
| 11 |       3 | R    | 2012-01-07 05:00:00 |   4000 | 2012-01-07 00:00:00 |
| 12 |       3 | A    | 2012-01-08 00:00:00 |  -1250 | 2012-01-07 05:00:00 |
| 14 |       2 | R    | 2012-01-09 00:00:00 |   2000 | 2012-01-04 13:23:01 |
| 13 |       3 | A    | 2012-01-10 00:00:00 |  -1500 | 2012-01-08 00:00:00 |
| 15 |       2 | A    | 2012-01-11 04:00:00 |  -2000 | 2012-01-09 00:00:00 |
| 16 |       2 | R    | 2012-01-12 00:00:00 |   5000 | 2012-01-11 04:00:00 |
+----+---------+------+---------------------+--------+---------------------+
13 rows in set (0.00 sec)

Используйте это как подзапрос, чтобы получить каждую транзакцию и ее предшественника в одной строке. Используйте некоторую фильтрацию для извлечения интересующих нас транзакций, а именно транзакций «A», чьи предшественники являются транзакциями «R», которые они точно отменяют —

SELECT
  t3.*,transactions.*
FROM
  transactions
  JOIN
  (SELECT t2.*,
          MAX(t1.date_time) AS prev_date
   FROM transactions t1
   JOIN transactions t2
   ON (t1.account = t2.account
      AND t2.date_time > t1.date_time)
   GROUP BY t2.account,t2.date_time) t3
  ON t3.account = transactions.account
     AND t3.prev_date = transactions.date_time
     AND t3.type='A'
     AND transactions.type='R'
     AND t3.amount + transactions.amount = 0
  ORDER BY t3.date_time;


+----+---------+------+---------------------+--------+---------------------+----+---------+------+---------------------+--------+
| id | account | type | date_time           | amount | prev_date           | id | account | type | date_time           | amount |
+----+---------+------+---------------------+--------+---------------------+----+---------+------+---------------------+--------+
|  3 |       3 | A    | 2012-01-03 13:10:01 |  -1500 | 2012-01-02 12:53:10 |  2 |       3 | R    | 2012-01-02 12:53:10 |   1500 |
|  6 |       2 | A    | 2012-01-04 13:23:01 |  -2000 | 2012-01-03 17:56:00 |  4 |       2 | R    | 2012-01-03 17:56:00 |   2000 |
|  9 |       3 | A    | 2012-01-06 17:24:01 |  -1250 | 2012-01-05 12:12:00 |  8 |       3 | R    | 2012-01-05 12:12:00 |   1250 |
| 15 |       2 | A    | 2012-01-11 04:00:00 |  -2000 | 2012-01-09 00:00:00 | 14 |       2 | R    | 2012-01-09 00:00:00 |   2000 |
+----+---------+------+---------------------+--------+---------------------+----+---------+------+---------------------+--------+
4 rows in set (0.00 sec)

Из приведенного выше результата видно, что мы почти у цели — мы идентифицировали нежелательные транзакции. Используя LEFT JOIN, мы можем отфильтровать их из всего набора транзакций:

SELECT
  transactions.*
FROM
  transactions
LEFT JOIN
  (SELECT
     transactions.id
   FROM
     transactions
     JOIN
     (SELECT t2.*,
             MAX(t1.date_time) AS prev_date
      FROM transactions t1
      JOIN transactions t2
      ON (t1.account = t2.account
         AND t2.date_time > t1.date_time)
      GROUP BY t2.account,t2.date_time) t3
     ON t3.account = transactions.account
        AND t3.prev_date = transactions.date_time
        AND t3.type='A'
        AND transactions.type='R'
        AND t3.amount + transactions.amount = 0) t4
  USING(id)
  WHERE t4.id IS NULL
    AND transactions.type = 'R'
  ORDER BY transactions.date_time;

+----+---------+------+---------------------+--------+
| id | account | type | date_time           | amount |
+----+---------+------+---------------------+--------+
|  1 |       1 | R    | 2012-01-01 10:01:00 |   1000 |
|  5 |       1 | R    | 2012-01-04 12:30:01 |   1000 |
|  7 |       3 | R    | 2012-01-04 15:13:10 |   3000 |
| 10 |       3 | R    | 2012-01-07 00:00:00 |   1250 |
| 11 |       3 | R    | 2012-01-07 05:00:00 |   4000 |
| 16 |       2 | R    | 2012-01-12 00:00:00 |   5000 |
+----+---------+------+---------------------+--------+
person gcbenison    schedule 28.02.2012
comment
да почти там! но почему в итоговом результате появляется 11, если оно отменяется на 12? - person Javier Novoa C.; 28.02.2012
comment
ОК, я неправильно понял вопрос; Я думал, что для того, чтобы A было отменой, его поле amount должно было быть отрицанием суммы в строке R. Избавление от предложения AND t3.amount + transactions.amount = 0 должно убрать это ограничение (и дать то, что вы ищете) - person gcbenison; 28.02.2012
comment
ждать! ты прав больше чем я! Я не заметил эту деталь, это, конечно, то, что я ищу :) Позвольте мне сделать дополнительные тесты! Я думаю, что это, наконец, правильный :) - person Javier Novoa C.; 29.02.2012

arrow_upward
1
arrow_downward

здесь я пробовал в MSSQL. Пожалуйста, проверьте логику и попробуйте в mysql. Я предполагаю, что логика заключается в том, чтобы сделать новую транзакцию после отмены первой транзакции. На вашей иллюстрации id = 7 создается после отмены id = 3.

Я проверил в mssql

create table Transactions(id int,account varchar(5),  tp char(1),date_time datetime,amount int)

insert into Transactions values (1,'001','R','2012-01-01 10:01:00',1000)
insert into Transactions values (2,'003','R','2012-01-02 12:53:10',1500)
insert into Transactions values (3,'003','A','2012-01-03 13:10:01',-1500)
insert into Transactions values (4,'002','R','2012-01-03 17:56:00',2000)
insert into Transactions values (5,'001','R','2012-01-04 12:30:01',1000)
insert into Transactions values (6,'002','A','2012-01-04 13:23:01',-2000)
insert into Transactions values (7,'003','R','2012-01-04 15:13:10',3000)


select t.id, t.account, t.date_time, t.amount
from Transactions t
where t.tp = 'R'
and not exists
(
    select account, date_time
    from Transactions
    where tp = 'A'
    and account = t.account
    and t.date_time < date_time
)
person Thit Lwin Oo    schedule 28.02.2012
comment
логика, которую вы предположили, верна, спасибо за ваше время :) На самом деле, если где-то после строки 7 была еще одна строка 'R' для счета 003, то она также должна быть показана. Но если после этого создать еще одну строку «А» для счета 003, то новая строка не появится, а 7 все равно должна появиться. Позвольте мне отредактировать мой пример, чтобы проиллюстрировать это. Судя по тому, что вы знаете о логике, которую вы использовали в этом скрипте, вы думаете, что он должен вести себя так? - person Javier Novoa C.; 28.02.2012
comment
Хорошо, лучше, чтобы вы предоставили достаточно иллюстративных данных, чтобы удовлетворить все, что вы хотите. Давай попробуй еще раз. - person Thit Lwin Oo; 28.02.2012
comment
Вы правы... Думая о проблеме, и, насколько я могу судить, это последние варианты использования, которые мне нужно включить... Тестирование с вашим скриптом как есть, строка 7 исключена, чего не должно быть. , так что нужны дальнейшие изменения :) Давайте подумаем... - person Javier Novoa C.; 28.02.2012
comment
Я отредактировал исходный пост с решением, которое я нашел сам, но оно требует дальнейшей работы, на всякий случай, если вы захотите взглянуть на него... - person Javier Novoa C.; 28.02.2012

arrow_upward
1
arrow_downward

(отредактировано 2) ПОПРОБУЙТЕ ЭТО:

 SELECT trans.tp as type,
trans.id as id,
 trans.amount as amount, 
trans.date_time as dt, 
trans.account as acct
FROM Transactions trans
WHERE trans.tp = 'R' 
AND trans.account NOT IN (SELECT t.account AS acct
   FROM Transactions t
 WHERE t.date_time > trans.date_time
 AND t.tp = 'A'
AND t.amount = (trans.amount)-((trans.amount)*2)
  ORDER BY t.date_time DESC
 )  ;
person Community    schedule 28.02.2012
comment
мммм возможно... просто сомнение... почему ‹= в предложении date_time where? предполагается, что строки «A» существуют ПОСЛЕ некоторой строки «R», поэтому для любого «R», который должен быть отменен, может быть строка «A», но ее дата будет в будущем «R». ' ряд, не в своем прошлом... - person Javier Novoa C.; 28.02.2012
comment
Если вы удалите его из предложения where, он даст вам правильный вывод? - person prukuhkoo; 28.02.2012
comment
Мне просто нужно проверить это, у меня много данных, поэтому тесты непростые. Я попросил просто посмотреть предположения в нижней части запроса (которых я просто не вижу прямо сейчас, возможно, мне нужно отдохнуть;), а затем посмотреть, могу ли я проверить его дальше или просто отбросить его, если они являются неправильные.. - person Javier Novoa C.; 28.02.2012
comment
хорошо, я проверил только данные, которые я использовал в своем примере, но он не показывает строку 7, см. комментарии к предыдущему ответу, я добавил строки 8 и 9, чтобы проиллюстрировать другой вариант использования :) Спасибо за ваше время тоже ! (Кстати, ‹= неправильно, должно быть ›, теперь с тестами я могу это сказать :) - person Javier Novoa C.; 28.02.2012
comment
Вы пытались удалить t.date_time › trans.date_time в предложении where подзапроса? я думаю, что это тот, который вызывает у вас проблемы .. - person prukuhkoo; 28.02.2012
comment
попробуйте удалить его в моем предыдущем запросе.. я думаю, что это действительно должно работать.. - person prukuhkoo; 28.02.2012
comment
да, но на самом деле выдает точно такой же результат, как и с этим пунктом! по крайней мере с примерными данными... - person Javier Novoa C.; 28.02.2012
comment
я отредактировал запрос выше, я попробовал его с примерными данными, которые были даны в предыдущем ответе, и это сработало ... он показывает только строки с 001 в качестве учетной записи. - person prukuhkoo; 28.02.2012
comment
спасибо, но дело не в том, что должны отображаться только строки с 001, а в том, что они не отменены... Я пробовал это редактирование с моими образцами данных, и все равно строка с id 7 (acct 003) не отображается появляется, что должно... :-/ возможно, я не очень хорошо объясняю нужную мне логику? - person Javier Novoa C.; 28.02.2012
comment
почти! Я заметил, что вы использовали тот факт, что сумма является отрицательной для каждой отмены, но что произойдет, если суммы в двух строках «R» для одной и той же учетной записи одинаковы? скажем, сумма для строки 8 также равна 3000, как и в строке 7 (и, конечно, сумма отмены строки 9 равна -3000). Тогда из-за условия в вашем скрипте 7-я строка тоже будет отброшена, но не должна... Как я уже сказал, почти! Еще раз спасибо! - person Javier Novoa C.; 28.02.2012
comment
Я отредактировал исходный пост с решением, которое я нашел сам, но оно требует дальнейшей работы, на всякий случай, если вы захотите взглянуть на него... - person Javier Novoa C.; 28.02.2012

arrow_upward
0
arrow_downward

Если ID действительно соответствует индексу строк при сортировке по дате_времени — как в примере (а если нет, вы можете создать такое поле ID) — вы можете сделать это:

SELECT t1.*
FROM transactions t1 JOIN transactions t2 ON(t2.id = t1.id + 1)
WHERE t1.type = 'R'
  AND NOT((t2.type = 'A') AND ((t1.amount + t2.amount) = 0))

т. е. использовать поле ID для получения каждой строки и ее преемника в одной и той же строке результата; затем отфильтруйте нужные свойства.

person gcbenison    schedule 28.02.2012
comment
Я пробовал это, но это не работает. Даже с данными, которые я использую в качестве примера (и это просто, я пытался сильно упростить свою модель, чтобы здесь была показана только суть проблемы), этот скрипт показывает мне строки с id 1,4,5,7 но с самого начала строка 4 не должна отображаться. Как вы можете видеть, строка 4 «отменяется» в строке 6, а не сразу в строке 5, поэтому увеличение идентификатора на 1 не работает для этого конкретного случая... В любом случае, тот факт, что идентификатор показывает, как будто индексация по дате просто артефакт моего примера... - person Javier Novoa C.; 28.02.2012
comment
@javier Хорошо, теперь я это вижу - сотри этот подход и посмотри мой новый ответ - person gcbenison; 28.02.2012

arrow_upward
0
arrow_downward

Чтобы улучшить свой запрос, попробуйте следующее:

SELECT t1.*, tFlagged.id AS cancId, tFlagged.tp AS cancFlag FROM t t1
LEFT JOIN t tFlagged
ON t1.account = tFlagged.account AND t1.date_time < tFlagged.date_time
WHERE t1.tp = 'R' 
GROUP BY t1.id
HAVING tFlagged.tp is null or tFlagged.tp <> 'A'

Он будет работать намного быстрее... надеюсь, даст те же результаты: P

person Mosty Mostacho    schedule 28.02.2012
comment
запрос для решения даже не имеет этого условия... и правильный запрос с аналогичными условиями нуждается в том, чтобы правильно фильтровать данные в соединении... - person Javier Novoa C.; 28.02.2012
comment
ок... позвольте мне тщательно проверить это с моими реальными данными. Запрос действительно быстрый по сравнению с тем, что у меня есть, но дает другое количество строк результатов, поэтому я должен сначала проанализировать его... Спасибо! - person Javier Novoa C.; 28.02.2012