Michał Lowas-Rzechonek

C / C++ / PYTHON

mrzechonek

* does not apply to mysql

WITH RECURSIVE breadcrumb AS (

  SELECT 0 as level, id, parent, name
   FROM section
   WHERE id = 4

 UNION ALL

  SELECT level + 1, s.id, s.parent, s.name
   FROM breadcrumb b
   JOIN section s ON b.parent = s.id

 )
 SELECT * FROM breadcrumb
 ORDER BY level DESC;

WITH pages AS (

  SELECT
    (row_number() OVER (ORDER BY surname) - 1) / 10
      AS page,
    name
  FROM person

)
SELECT DISTINCT
    page,
    first_value(name) OVER pagination AS "from",
    last_value(name) OVER pagination AS "to"
FROM pages
WINDOW pagination AS (PARTITION BY page)
ORDER BY page;

SELECT
    "building",
    date_trunc('month', "date") AS "month",
    count(*) AS "count"
FROM "lectures_lecture"
LEFT JOIN "lectures_room"
       ON "lectures_room"."id" = "room_id"
GROUP BY "building", "month"

WITH "busy_months" AS (...)

SELECT DISTINCT ON ("building")
    "building",
    first_value("month") OVER "busy_months"
    last_value("month") OVER "busy_months"
FROM
    "busy_months"
WINDOW "busy_months" AS (
    PARTITION BY "building"
    ORDER BY "count"
    ROWS BETWEEN UNBOUNDED PRECEDING AND
                 UNBOUNDED FOLLOWING
);

SELECT
    message ->> 'uri'
FROM
    event
WHERE
    message ->> 'status' = '404';



-- go to 11
CREATE INDEX
    journal_event_message_response
ON journal_event((message ->> 'response'));

CREATE LANGUAGE plythonu;

CREATE FUNCTION polynomial_fit(y FLOAT[],
                               degree INTEGER)
    RETURNS FLOAT[] AS
$$
    import numpy
    x = numpy.arange(len(y))
    return numpy.polyfit(x, y, degree)
$$
LANGUAGE plpythonu IMMUTABLE;

mrzechonek