3 ms·
LLMs are far more reliable at producing something like this: { "dimensions": [ "users.state", "users.city", "orders.status"
by mritchie712 1y ago
LLMs are far more reliable at producing something like this:
{
"dimensions": [
"users.state",
"users.city",
"orders.status"
],
"measures": [
"orders.count"
],
"filters": [
{
"member": "users.state",
"operator": "notEquals",
"values": ["us-wa"]
}
],
"timeDimensions": [
{
"dimension": "orders.created_at",
"dateRange": ["2020-01-01", "2021-01-01"]
}
],
"limit": 10
}
than this:
SELECT
users.state,
users.city,
orders.status,
sum(orders.count)
FROM orders
CROSS JOIN users
WHERE
users.state != 'us-wa'
AND orders.created_at BETWEEN '2020-01-01' AND '2021-01-01'
GROUP BY 1, 2, 3
LIMIT 10;
- Agraillo 1y agoThe programming languages are more predictable than human. So the rules are much easier to be "compressed" after they're basically detected when fed with big data. Your two examples imho are easily interchangeable during follow-up conversation with a decent LLM. Tested this with the following prompt and fed a c fragment and an SQL-fragment, got in both cases something like your first one > Please convert the following fragment of a programming language (auto-detect) into a json-like parsing information when language construct is represented like an object, fixed branches are represented like properties and iterative clauses (statement list for example) as array.
- sgarland 1y agoThis doesn't make sense. From a schema standpoint, table `orders` presumably has a row per order, with columns like `user_id`, `status` (as you stated), `created_at` (same), etc. Why would there be a `count` column? What does that represent? From a query standpoint, I'm not sure what this would accomplish. You want the cartesian product of `users` and `orders`, filtered to all states except Washington, and where the order was created in 2020? The only reason I can think of to use a CROSS JOIN would be if there is no logical link between the tables, but that doesn't make any sense for this, because users:orders should be a 1:M relationship. Orders don't place themselves. I think what you might have meant would be: SELECT users.state, users.city, orders.status, COUNT(*) FROM users JOIN orders ON user.id = orders.user_id WHERE users.state != 'us-wa' AND orders.created_at BETWEEN '2020-01-01' AND '2021-01-01' GROUP BY 1, 2, 3 LIMIT 10; Though without an ORDER BY, this has no significant meaning, and is a random sampling at best. Also, if you or anyone else is creating a schema like this, _please_ don't make this denormalized mess. `orders.status` is going to be extremely low cardinality, as is `users.state` (to a lesser extent), and `users.city` (to an even lesser extent, but still). Make separate lookup tables for `city` and/or `state` (you don't even need to worry about pre-populating these, you can use GeoNames[0]). For `status`, you could do the same, or turn them into native ENUM [1] if you'd like to save a lookup. [0]: https://www.geonames.org https://www.geonames.org [1]: https://www.postgresql.org/docs/current/datatype-enum.html https://www.postgresql.org/docs/current/datatype-enum.html