4 ms·
How do you do SELECT AVG(price) FROM cars WHERE brand='BMW' GROUP BY year ORDER BY year without SQL? So far, every alternative syntax I have
by mg 2y ago
How do you do
SELECT AVG(price) FROM cars
WHERE brand='BMW'
GROUP BY year
ORDER BY year
without SQL?
So far, every alternative syntax I have encountered was less human readable and/or less concise.
I think SQL will never be replaced because it is not really something that was invented but rather the simple consequence of wanting to store and retreive data.
You could replace "SELECT" with "GET" or "FETCH". But in the end you need to define what action you want to perform. You could replace "WHERE" with "FILTER" or "CONDITION". But in the end you need to define what you want to retreive. Etc. So by just stating the neccessary information, you always end up with SQL. And every real alternative has the problem of being less concise, less easy to read and less easy to write.
- e_i_pi_2 2y agoThis is just a map/filter/reduce/sort, you can do that in pretty much any language you want
- randomdata 2y agoUse QUEL? range of c is cars retrieve (avg(c.price) where c.brand = 'BMW' by c.year)
- mg 2y agoIt seems either the group or the order is missing?
- deleted 2y ago[deleted]
- randomdata 2y agoYes, QUEL is a language for querying relational databases. Relations are fundamentally unordered. I suppose if your example is using a NoSQL (meaning non-relational) database, that is ironically queried using SQL, then you might need something else. But my understanding is that we're back on the relational kick these days.
- zwischenzug 2y agoAuthor here. Mongodb offers primitives around filtering, group bys and orders, but calls them 'pipelines'. I'm not an expert on that, but it's quite possible. It's just a series of set and list operations after all.
- mg 2y agoIt is certainly possible, but is it as lean and readable?
- zwischenzug 2y agoNo.
- francisofascii 2y agoIs it a skill, though? Ask ChatGPT, "In SQL, given a cars table, with a price field a brand field, and a year field, how do you get the average price of each brand for each year...?
- raxxorraxor 2y agoChatGPT and similar LLM will get anything more complex and interesting wrong though, even the models specialized in SQL. And writing the query in SQL is probably also faster than trying to write down the prompt. Sometimes it can be used to optimize your queries, but it isn't its strength either.
- pavel_lishin 2y agoA good ORM will mostly get out of the way and let you write something like return Cars .query() .select(avg('price')) .where( { brand: "BMW" }) .groupBy('year') .orderBy('year')
- mg 2y agoLike all alternatives to SQL I have seen, this is not as lean and readable as SQL.
- randomdata 2y agoThe SQL is preferable in this overly simple, cherry-picked case, but now do composition.
- pavel_lishin 2y agoI've done composition in Objection ORM, and honestly, it's a fair amount of hassle. On our projects, which are moderately small, I genuinely don't know if it is/was worth the hassle of setting up, vs. writing some repeated code. (We also have a pretty decent testing culture in place, so I would be much more accepting of DRY-violations.)
- randomdata 2y agoI assume the above is based on said package? Wherein lies the hassle? bmws = Cars.query().where( { brand: "BMW" }).groupBy('year').orderBy('year') bmwAvg = bmws.select(avg('price')) bmwMin = bmws.select(min('price')) bmwMax = bmws.select(max('price'))
- pavel_lishin 2y agoI totally agree - but this has the advantage of returning something like a well-defined object that hews to an interface, as long as you have the model set up. The trade-offs are usually worth it, vs, doing something like: const row = db.query('select whatever from wherever'); const obj = { id: row.id, brand: row.brand, ... etc };
- corytheboyd 2y agoEveryone here missing the point. Yeah it’s doable elsewhere, but the SQL is so perfectly readable.
- NikkiA 2y agoThe filtering on 'BMW' is the only aspect of that query that the DB server should be spending its resources on. Avg, grouping, and sorting the results are the domain of the front end that has to go through them and pretty print them anyway
- mg 2y agoYou want to drop the ability to group data from database software? Some issues come to mind: Grouping on the client would mean that you have to send the ungrouped data over the wire/air. Which might be orders of magnitude more data than the client needs. What if "cars" is a table of car photos made over the last 15 years and there are 20 million photos of BMWs in there? You send all the 20 million rows to the client to crunch it down to 15 rows? The ungrouped data might be something the client is not allowed to see. Depending on the type of grouping, the client would have to reimplement fast and efficient algorithms that have been tested and optimized for decades in RDBMs. Good luck, matching that by writing some JS for the browser.
- thiht 2y agoPRQL translation: from cars filter brand == "BMW" group year ( aggregate [ average_price = average price ] ) sort year select average_price A bit longer, but I love that it reads from top to bottom. When you have a complex SQL query, I’ve sometimes found it easier to write it as PRQL and then convert it to SQL https://prql-lang.org/ https://prql-lang.org/