Funciones de ventana

• Ultima modificación: Mar 6, 2024 | YouTube

Preparación

import sqlite3

from IPython.core.magic import register_cell_magic, register_line_magic

conn = sqlite3.connect(":memory:")
cur = conn.cursor()


@register_line_magic
def sql(line):
    return cur.execute(line).fetchall()


@register_cell_magic
def sqlite3(line, cell):
    cell = [
        l for l in cell.split("\n") if l[0:2] != "--" and l[0:2] not in ["--", "##"]
    ]
    cell = "\n".join(cell)
    return cur.execute(cell).fetchall()


@register_cell_magic
def sqlite3script(line, cell):
    cell = [l for l in cell.split("\n") if len(l) >= 2 and l[0:2] not in ["--", "##"]]
    cell = "\n".join(cell)
    conn.executescript(cell)
    conn.commit()

Datos

%%sqlite3script

DROP TABLE IF EXISTS t1;

CREATE TABLE t1(
    a INTEGER PRIMARY KEY,
    b,
    c
);

INSERT INTO
    t1
VALUES
    (1, 'G', 'one'  ),
    (2, 'F', 'two'  ),
    (3, 'E', 'three'),
    (4, 'D', 'one'  ),
    (5, 'C', 'two'  ),
    (6, 'B', 'three'),
    (7, 'A', 'one'  );

Conceptos fundamentales

• Son funciones SQL cuyas entradas son tomadas de una “ventana” conformada por una o más filas en los resultados de una clausula SELECT.

• Se distingen de otras funciones SQL porque usan la palabra reservada OVER.

• Las funciones de ventana también pueden tener una clausula FILTER entre la función y la clausula OVER.

• No pueden usar DISTINCT

• Las funciones de agregación no cambian el número de filas retornadas y agregan a cada fila el resultado de la función de agregación.

%%sqlite3

SELECT
    a, b, c, max(a)
OVER
(
    PARTITION BY c
)
FROM
    t1;

[(1, 'G', 'one', 7),
 (4, 'D', 'one', 7),
 (7, 'A', 'one', 7),
 (3, 'E', 'three', 6),
 (6, 'B', 'three', 6),
 (2, 'F', 'two', 5),
 (5, 'C', 'two', 5)]

%%sqlite3

SELECT
    a, b, c, rank()
OVER
(
    PARTITION BY c
    ORDER BY b
)
FROM
    t1;

[(7, 'A', 'one', 1),
 (4, 'D', 'one', 2),
 (1, 'G', 'one', 3),
 (6, 'B', 'three', 1),
 (3, 'E', 'three', 2),
 (5, 'C', 'two', 1),
 (2, 'F', 'two', 2)]

%%sqlite3

SELECT
    a, b, group_concat(b, '.')
OVER
(
    ORDER BY
        a
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
AS
    group_concat
FROM
    t1;

[(1, 'G', 'G.F'),
 (2, 'F', 'G.F.E'),
 (3, 'E', 'F.E.D'),
 (4, 'D', 'E.D.C'),
 (5, 'C', 'D.C.B'),
 (6, 'B', 'C.B.A'),
 (7, 'A', 'B.A')]

• La clausula PARTITION BY permite dividir el resultado de la consulta en una o más particiones. Una partición esta conformada por todas las filas que tienen el mismo valor para todos los terminos de la clausula PARTITION BY.

%%sqlite3

SELECT
    c, a, b, group_concat(b, '.')
OVER
(
    PARTITION BY c
    ORDER BY a
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
AS
    group_concat
FROM
    t1
ORDER BY
    c, a;

[('one', 1, 'G', 'G.D.A'),
 ('one', 4, 'D', 'D.A'),
 ('one', 7, 'A', 'A'),
 ('three', 3, 'E', 'E.B'),
 ('three', 6, 'B', 'B'),
 ('two', 2, 'F', 'F.C'),
 ('two', 5, 'C', 'C')]

%%sqlite3

SELECT
    c, a, b, group_concat(b, '.')
OVER
(
    PARTITION BY c
    ORDER BY a
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
AS
    group_concat
FROM
    t1
ORDER BY
    a;

[('one', 1, 'G', 'G.D.A'),
 ('two', 2, 'F', 'F.C'),
 ('three', 3, 'E', 'E.B'),
 ('one', 4, 'D', 'D.A'),
 ('two', 5, 'C', 'C'),
 ('three', 6, 'B', 'B'),
 ('one', 7, 'A', 'A')]

%%sqlite3

SELECT
    a, b, c, group_concat(b, '.')
OVER
(
    ORDER BY c
)
AS
    group_concat
FROM
    t1
ORDER BY
    c;

[(1, 'G', 'one', 'G.D.A'),
 (4, 'D', 'one', 'G.D.A'),
 (7, 'A', 'one', 'G.D.A'),
 (3, 'E', 'three', 'G.D.A.E.B'),
 (6, 'B', 'three', 'G.D.A.E.B'),
 (2, 'F', 'two', 'G.D.A.E.B.F.C'),
 (5, 'C', 'two', 'G.D.A.E.B.F.C')]

%%sqlite3

SELECT
    c, a, b, group_concat(b, '.')
OVER
(
    ORDER BY c, a
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
AS
    group_concat
FROM
    t1
ORDER BY
    c, a;

[('one', 1, 'G', 'G.D.A.E.B.F.C'),
 ('one', 4, 'D', 'D.A.E.B.F.C'),
 ('one', 7, 'A', 'A.E.B.F.C'),
 ('three', 3, 'E', 'E.B.F.C'),
 ('three', 6, 'B', 'B.F.C'),
 ('two', 2, 'F', 'F.C'),
 ('two', 5, 'C', 'C')]

%%sqlite3

SELECT
    c, a, b, group_concat(b, '.')
FILTER
(
    WHERE c!='two'
)
OVER
(
  ORDER BY a
)
AS
    group_concat
FROM
    t1
ORDER BY
    a;

[('one', 1, 'G', 'G'),
 ('two', 2, 'F', 'G'),
 ('three', 3, 'E', 'G.E'),
 ('one', 4, 'D', 'G.E.D'),
 ('two', 5, 'C', 'G.E.D'),
 ('three', 6, 'B', 'G.E.D.B'),
 ('one', 7, 'A', 'G.E.D.B.A')]