ejemplos - tipos de consultas en mysql

Consulta para mostrar los créditos gastados de la tabla transaccional

Question

Estoy trabajando con una tabla que contiene transacciones de crédito en las que quiero mostrar quiénes se gastaron los créditos cuando se realizó una venta.

En la mesa:

• Credits son agregados por una entidad usando un código de entidad único (registrado en la columna GivenByUserCode )
• Las adiciones de crédito siempre tienen tal código.
• Los créditos que se gastan siempre tendrán un valor negativo.
• Los créditos que se gastan no tendrán un código de entidad (el valor de GivenByUserCode es null ).

Utilizando los datos anteriores como ejemplo, si un usuario realiza una compra el 2018-01-02 el informe debe mostrar todos estos créditos originados en BM01 . La complejidad de la adición es que una compra se puede dividir en varias adiciones, consulte la compra en 2018-02-03 que se divide en 3 adiciones.

Creo que la solución tendrá algo que ver con el uso de cte y over pero no tengo experiencia en el uso de estos. Encontré un problema similar (no el mismo) en SqlServerCentral .

Cualquier ayuda / dirección sería más apreciada.

Entrada y DDL

DECLARE @CreditLogs TABLE(CreditLogId int not null identity(1,1), Credits INT NOT NULL, OccurredOn DATETIME2(7) NOT NULL, GivenByUserCode VARCHAR(100) NULL)

INSERT INTO @CreditLogs (Credits, OccurredOn, GivenByUserCode) VALUES
  (10,  ''2018-01-01'', ''BM01'')
, (10,  ''2018-01-01'', ''BM01'')
, (-10, ''2018-01-02'', NULL)
, (-5,  ''2018-01-04'', NULL)
, (5,   ''2018-02-01'', ''SP99'')
, (40,  ''2018-02-02'', ''BM02'')
, (-40, ''2018-02-03'', NULL)
, (-4,  ''2018-03-05'', NULL)

Entrada en forma de tabla

CreditLogId | Credits | OccurredOn | GivenByUserCode
------------+---------+------------+----------------
          1 |      10 | 2018-01-01 |            BM01
          2 |      10 | 2018-01-01 |            BM01
          3 |     -10 | 2018-01-02 |            NULL
          4 |      -5 | 2018-01-04 |            NULL
          5 |       5 | 2018-02-01 |            SP99
          6 |      40 | 2018-02-02 |            BM02
          7 |     -40 | 2018-02-03 |            NULL
          8 |      -4 | 2018-03-05 |            NULL

Rendimiento esperado

SELECT *
FROM (VALUES
     (3, ''2018-01-02'', 10, ''BM01'')
    ,(4, ''2018-01-04'', 5, ''BM01'')
    ,(7, ''2018-02-03'', 5, ''BM01'')
    ,(7, ''2018-02-03'', 5, ''SP99'')
    ,(7, ''2018-02-03'', 30, ''BM02'')
    ,(8, ''2018-03-05'', 4, ''BM02'')
) expectedOut (CreditLogId, OccurredOn, Credits, GivenByUserCode)

Produce salida

CreditLogId | Occurred on | Credits | GivenByUserCode
------------+-------------+---------+----------------
          3 |  2018-01-02 |      10 |            BM01
          4 |  2018-01-04 |       5 |            BM01
          7 |  2018-02-03 |       5 |            BM01
          7 |  2018-02-03 |       5 |            SP99
          7 |  2018-02-03 |      30 |            BM02
          8 |  2018-03-05 |       4 |            BM02

Código hasta ahora

No es mucho y no estoy seguro de a dónde ir desde aquí.

WITH totals AS (
    SELECT CreditLogId, OccurredOn, credits, sum(credits) OVER(ORDER BY OccurredOn) AS TotalSpent
    FROM @CreditLogs
    WHERE Credits < 0
)
SELECT *
FROM totals

Aclaración adicional

La salida esperada es para cada monto de crédito gastado de donde provienen esos créditos. Los créditos se gastan primero en entrar, primero en salir (FIFO). Aquí una explicación de cada valor en la salida de muestra con la esperanza de que esto aclare la salida deseada.

• Para el gasto de 10 créditos (ID de registro de crédito 3) se puede rastrear a una adición de ID de registro de crédito 1
• Para el gasto de 5 créditos (la identificación de registro de crédito 4) se puede rastrear hasta una adición de la identificación de registro de crédito 2 (ya que la identificación de registro de crédito 1 se "agotó")
• Para el gasto de 40 créditos en el registro de crédito, la identificación 7 se remonta a
  • El resto de la adición de la identificación de registro de crédito 2, 5 créditos
  • ID de registro de crédito 5 (adición de 5)
  • ID de registro de crédito 6 (además de 40, 10 restantes)
• Para el gasto de 4 créditos en el registro de crédito 8, se utiliza el saldo del registro de crédito id. 6

Tenga en cuenta que queda un saldo total de 6 créditos, el saldo no tiene que ser cero, pero nunca será negativo, ya que los usuarios solo pueden gastar lo que tienen.

Answer 1

prueba esto:

WITH Credits_added AS (
    SELECT CreditLogId, OccurredOn, credits
    , SUM(credits) OVER (ORDER BY CreditLogId) - credits AS b --before
    , SUM(credits) OVER (ORDER BY CreditLogId) AS a --after
    , GivenByUserCode
    FROM @CreditLogs
    WHERE Credits > 0)
, Credits_spent AS (
    SELECT CreditLogId, OccurredOn, credits
    , SUM(credits) OVER (ORDER BY CreditLogId) * -1 + credits AS b
    , SUM(credits) OVER (ORDER BY CreditLogId) * -1 AS a
    FROM @CreditLogs
    WHERE Credits < 0)
SELECT s.CreditLogId, s.OccurredOn
, CASE WHEN a.a > s.a THEN s.a ELSE a.a END - CASE WHEN a.b > s.b THEN a.b ELSE s.b END AS Credits 
, a.GivenByUserCode
FROM Credits_added AS a
INNER JOIN Credits_spent AS s ON a.a > s.b AND s.a > a.b

Answer 2

ya que mencionó que habrá de 4 a 5 millones de registros por año, incluso si esto se puede hacer por consulta, será una consulta lenta.

Sugeriría tener otra tabla como creditSpent que contenga (PurchaseCreditLogId , additionCreditLogId, Amount) creditSpent (PurchaseCreditLogId , additionCreditLogId, Amount)

Y en el momento de insertar compras, busque todos los registros, calcule la cantidad que debe reducirse de cada uno y almacene esa información en esa tabla

Luego, cuando esté ejecutando su informe, puede hacer una consulta simple en esta tabla