4 ms·
are you seriously consider this declare: eql: operator: = left: $1 value: $2 get_sum: operator: $1 sum: apply: ["get_sum", "sum"]
by heywhatupboys 4y ago
are you seriously consider
this
declare:
eql:
operator: =
left: $1
value: $2
get_sum:
operator: $1
sum:
apply: ["get_sum", "sum"]
args:
- $1
group_by:
field: $1
main:
from: payments
alias: p
distinct: true
select:
- apply: ["sum", "i.views"]
- apply: ["sum", "i.clicks"]
- apply: ["sum", "p.amount"]
where:
- apply: ["eql", "user_id", "12"]
group:
- apply: ["group_by", "date"]
more readable than this?
SELECT
DISTINCT SUM(i.views),
SUM(i.clicks),
SUM(p.amount)
FROM
"payments" "p"
WHERE
user_id = 12
GROUP BY
date
- revskill 4y agoAs i said, it's about maintainability, not about length ;) All that declaration can be reused and implicitly imported. SO main query is just simple as main: from: payments alias: p distinct: true select: - apply: ["sum", "i.views"] - apply: ["sum", "i.clicks"] - apply: ["sum", "p.amount"] where: - apply: ["eql", "user_id", "12"] group: - apply: ["group_by", "date"]
- simonw 4y agoI'm not seeing the readability benefit over this (which I reformatted and converted to lower-case, personal taste): select distinct sum(i.views), sum(i.clicks), sum(p.amount) from "payments" "p" where user_id = 12 group by date
- revskill 4y agoThen try this query instead ? https://yaml2sql.netlify.app/play/crosstab/ https://yaml2sql.netlify.app/play/crosstab/ How do you think ?
- simonw 4y agoWow, I'd never seen that crosstab() function before! TIL. https://www.postgresql.org/docs/current/tablefunc.html#id-1.11.7.52.5.5 https://www.postgresql.org/docs/current/tablefunc.html#id-1.... This raises a problem with the YAML DSL though. Consider the following (truncated) example: main: select: - field: ct.* from: alias: ct fields: ... crosstab: - select: - extra_infos.problem_id - extra_infos.info_type - extra_infos.info_value from: extra_infos - select: - extra_infos.info_type distinct: true from: extra_infos Inside the "from:" area, those alias and fields and crosstab keys are all at the same level. But... "alias:" and "fields:" correspond to features of the from clause itself, whereas "crosstab:" is a special custom thing (a macro?) which ends up being compiled into a call to the identically-named crosstab() feature. So to understand the YAML, I need to understand both how the crosstab() feature in PostgreSQL works AND how that YAML DSL represents "crosstab:" calls using a different syntax. I looked up crosstab in the PostgreSQL manual but that information won't help me figure out the DSL. So I think I'd rather stick with regular SQL.
- revskill 4y agoMy goal is to build a composable and consistent DSL. It should have few keywords and structures as possible. Of course there's some "fixed keyword" like above crosstab (due to inconsistent SQL syntax i think). "alias" + "fields" helps you build "as (field1, field2,...)" kind of thing. "select" + "from" must form a normal "sql query".