cwida / duckpgq-extension

DuckDB extension that adds support for SQL/PGQ
https://duckpgq.notion.site/b8ac652667964f958bfada1c3e53f1bb?v=3b47a8d44bdf4e0c8b503bf23f1b76f2
MIT License
73 stars 7 forks source link

WITH statements and subqueries now seem to work #86

Closed Dtenwolde closed 9 months ago

Dtenwolde commented 9 months ago

Fixes #85

The following queries are now possible

-FROM GRAPH_TABLE (snb_projected
    MATCH (m:message)
    COLUMNS (m.id)
    ) tmp, (SELECT id from message limit 1)
LIMIT 10;
-WITH message_count AS (
    SELECT count(*) as m_count
    FROM Message m
    WHERE m.creationDate < '2010-05-27 11:16:36.013'
)
SELECT year, isComment,
    CASE    WHEN m_length < 40 THEN 0
            WHEN m_length < 80 THEN 1
            WHEN m_length < 160 THEN 2
            ELSE 3 END as lengthCategory,
    count(*) as messageCount,
    avg(m_length) as averageMessageLength,
    sum(m_length) as sumMessageLength,
    count(*) / mc.m_count as percentageOfMessages
FROM GRAPH_TABLE(snb_projected
    MATCH (message:Message where message.creationDate < '2010-05-27 11:16:36.013')
    COLUMNS (date_part('year', message.creationDate::TIMESTAMP) as year, message.ImageFile is NULL as isComment, message.length as m_length, message.id)
    ) tmp, message_count mc
GROUP BY year, isComment, lengthCategory, m_count
ORDER BY year DESC, isComment ASC, lengthCategory ASC;