Funciones de agregación

• 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 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 de prueba

%%sqlite3script

DROP TABLE IF EXISTS demo;

CREATE TABLE demo (
  a STRING,
  n FLOAT
);

INSERT INTO demo
VALUES
    ('a', 1),
    ('a', 2),
    ('b', 3),
    ('b', 4),
    ('b', 5);

Funciones

##
## avg(X)
##
%sql SELECT a, avg(n) FROM demo GROUP BY a;

[('a', 1.5), ('b', 4.0)]

##
## count(*)
##   retorna la cantidad de filas en un grupo
## count(X)
##   count(X) cuenta la cantidad de registros no nulos.
##
%sql SELECT a, count(*) FROM demo GROUP BY a;

[('a', 2), ('b', 3)]

##
## max(X)
##
%sql SELECT a, max(n) FROM demo GROUP BY a;

[('a', 2.0), ('b', 5.0)]

##
## min(X)
##
%sql SELECT a, min(n) FROM demo GROUP BY a;

[('a', 1.0), ('b', 3.0)]

##
## sum(X)
##
%sql SELECT a, sum(n) FROM demo GROUP BY a;

[('a', 3.0), ('b', 12.0)]