New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Prettify SQL for Resource Queries #222

Open
jqnatividad opened this Issue Sep 13, 2018 · 3 comments

Comments

Projects
None yet
2 participants
@jqnatividad

jqnatividad commented Sep 13, 2018

Resource Queries are pretty powerful, as the data publisher has access to the full expressive power of PostgreSQL - not just a SQL dialect that cannot do joins, computed columns, aggregations, spatial queries, etc.

For example, this complex SQL to do aggregations for a viz was handled without problems using the Resource Query feature:

-- first, get total number of cases
WITH "TotalCases" AS
  ( SELECT COUNT(*)::NUMERIC AS "TotalCases"
   FROM "b9517753-b821-4786-81eb-c94ca9be6b2d"), 

-- get all closed cases
"ClosedCases" AS
  ( SELECT "DaysToClose"::NUMERIC AS "nDaysToClose"
   FROM "b9517753-b821-4786-81eb-c94ca9be6b2d"
   WHERE "DaysToClose" != 'NULL' ), 

-- count all closed cases
"TotalClosedCases" AS
  ( SELECT COUNT(*)::NUMERIC AS "TotalClosed"
   FROM "ClosedCases"), 

-- count cases that were closed < 30 days
"TotalClosedCases30" AS
  (SELECT COUNT(*)::NUMERIC AS "TotalClosed30"
   FROM "ClosedCases"
   WHERE "nDaysToClose" <= 30 ), 

-- count cases that were closed between 30 and 60 days
"TotalClosedCases3060" AS
  ( SELECT COUNT(*)::NUMERIC AS "TotalClosed3060"
   FROM "ClosedCases"
   WHERE "nDaysToClose" > 30
     AND "nDaysToClose" <= 60 ), 

-- count cases that were closed between 60 and 90 days
"TotalClosedCases6090" AS
  ( SELECT COUNT(*)::NUMERIC AS "TotalClosed6090"
   FROM "ClosedCases"
   WHERE "nDaysToClose"> 60
     AND "nDaysToClose" <= 90 ), 

-- count cases that took longer than 90 days
"TotalClosedCases90" AS
  ( SELECT COUNT(*)::NUMERIC AS "TotalClosed90"
   FROM "ClosedCases"
   WHERE "nDaysToClose" > 90 ) 

-- compute the metrics
SELECT "TotalCases",
       "TotalClosed",
       ("TotalCases"- "TotalClosed") AS "OpenCases",
       ROUND((("TotalCases"- "TotalClosed")/"TotalCases") * 100.00, 2) AS "% Open",
       "TotalClosed30",
       ROUND(("TotalClosed30"/"TotalCases") * 100.00, 2) AS "% Closed < 30",
       "TotalClosed3060",
       ROUND(("TotalClosed3060"/"TotalCases") * 100.00, 2) AS "% Closed bet 30 and 60",
       "TotalClosed6090",
       ROUND(("TotalClosed6090"/"TotalCases") * 100.00, 2) AS "% Closed bet 60 and 90",
       "TotalClosed90",
       ROUND(("TotalClosed90"/"TotalCases") * 100.00, 2) AS "% Closed > 90"
FROM "TotalCases",
     "TotalClosedCases",
     "TotalClosedCases30",
     "TotalClosedCases3060",
     "TotalClosedCases6090",
     "TotalClosedCases90"

image

In actuality, though, the SQL above was pretty-printed, and here is what it actually looks like when defining it in CKAN:

image

And that's after adding whitespace to make it more readable.

Would be nice if there's a FORMAT button to re-format the Resource Query, perhaps, by using something like sqlparse.

@wardi

This comment has been minimized.

Contributor

wardi commented Sep 14, 2018

This should be 100% CSS and javascript built on something from https://en.wikipedia.org/wiki/Comparison_of_JavaScript-based_source_code_editors

@jqnatividad

This comment has been minimized.

jqnatividad commented Sep 14, 2018

Perhaps, #78 can be revisited to not only enable this, but to improve the UX in general when entering freetext that requires formatting.

@wardi

This comment has been minimized.

Contributor

wardi commented Sep 14, 2018

That's a great idea. No reason to have two rich JS text editors

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment