18 ms·
Ask HN: Does anyone else think SQL needs help?
I have been writing a decent amount of SQL for three years now, and while the paradigm is extremely powerful, the language lends itself to unmaintainable spaghetti code quickly.
Fundamentally, SQL lacks meaningful abstraction, and there's no sane way to package very common operations.
Say you want to find elements in a row that correspond to some maximum value when grouped by date. Today, you'd need to write something like this EVERY SINGLE TIME:
```
SELECT
sd1.sale_person_id,
sd1.sale_person_name,
sd1.no_products_sold,
sd1.commission_percentage,
sd1.sales_department
FROM
sales_department_details sd1
INNER JOIN
(SELECT
sale_person_id, MAX(no_products_sold) AS no_products_sold
FROM
sales_department_details
GROUP BY sale_person_id) sd2
ON sd1.sale_person_id = sd2.sale_person_id
AND sd1.no_products_sold = sd2.no_products_sold;
```
Wouldn't something like this be nicer?
```
SELECT
sale_person_id,
max(no_products_sold) as max_sold,
link(sale_person_name, max_sold),
FROM
sales_department_details
```
Frankly, it seems like some sort of macro system is needed. Perhaps the SQL compiles into the above?
- pdntspa 4y agoI can't believe that at time of this writing, only a few other comments are mentioning VIEWs! Like that is this dude's exact complaint! smh...
- default-kramer 4y agoLet's say you have 14 derived facts (eg aggregations, computations involving joined tables, etc) about a SalesPerson. Do create 14 single-column views? Or have one view with 14 columns? Either way the composability is terrible. If you go with 14 views, the syntax is awful and not very resistant to refactoring. If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).
- pdntspa 4y agoYou can SELECT just the columns you want with one view, and IIRC that will exclude other columns from being retrieved unless they are the source of a derived value.
- wswope 4y ago> If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization). This is outdated; most modern DBs treat views as they would CTEs. If you don’t reference a defined field downstream, it doesn’t need to get calculated.
- default-kramer 4y agoIt's more about the fact that a join can change the shape of the result set. Even if the columns being projected aren't surfaced, the DB still has to process the joins to make sure the result set has the correct shape. For example, a human might know that a join will always be one:one or one:zero-or-one, but the DB has no choice but to make sure. Perhaps using subqueries instead of joins would work, but that gets ugly too. (Or maybe my knowledge is outdated and the optimizers have gotten way better than they were 3-4 years ago.)
- jdmichal 4y agoAt least PostgreSQL's query optimizer can and will drop `LEFT JOIN` clauses if the data is not actually being used. It can't do that for `INNER JOIN` because it must verify that a matching row exists.
- cm2187 4y agoFor LEFT join it would also need to know the combination of columns you are joining by are unique in the right table, which will be the case in many scenarios (joining on a primary key) but not in the general case.
- trimethylpurine 4y agoI'll add that MSSQL at least since 2019 will automatically modify the execution plan to avoid this by the second time the query is executed.
- 4y ago
- zasdffaa 4y agoAs others have said, any decent optimiser will prune out values that aren't SELECT'ed. I don't think it's that complex either - find the unused columns, remove them from subviews, recurse all the way down. Try it on MSSQL/Postgres and you can see it being done.
- fny 4y agoThe bigger problem is views are tightly coupled to the underlying data.
- saltcured 4y agoRight, there's a big difference between views and the kind of generic abstraction that I think the original poster seeks. You do need something like macros or generated SQL to build a library of algorithms you can apply to data. Imagine wanting something as simple as "find_percentile_value(table, column, percentile)". There is no portable and standard way to write this parametric abstraction and then reuse it on any column source you need to interrogate later.
- gerdesj 4y agoPlease give a simple example of what your second para means. Perhaps find_% of all values in the row vs the referenced column, in the referenced table. Now, ... reuse. Surely you write the result to another table and then refer to that.
- saltcured 4y agoThe detail of the function isn't that important, but I was thinking of something like the numpy percentile function as an example of a simple functional abstraction. You give it an array representing the population of values and a desired percentile (0..100), and it returns the value corresponding to that desired percentile. Calling it for percentile 50 would return the median value. Other options might select alternative methods for tie-breaking or interpolation between population samples. Note, this was just meant as an example where a view is not helpful. We don't always desire particular calculated data to be named for reuse. Rather, we want a reusable method we can apply to any data. In my earlier post, I tried to address the topic of macros or generated SQL, and so suggested it take a table and column name as input to expand a query idiom for a set of values stored in a table. But, that's not how I'd really want to do it. The rest of this comment will veer into a more elaborate perspective on real functional libraries, rather than mere macros... Today, with dialect-specific mechanisms, I can try to define window functions or ordered-set aggregate functions. It would be possible to solve the percentile problem this way. In fact, many SQL dialects have built-in functions that can help with percentiles, since this is recognized as such a basic statistical concept. But, I only chose percentile as an accessible yet concrete example. These built-in solutions are not the answer to my question. My question is how can user programmers extend the system with their own abstractions. Writing aggregate and window functions requires a big mental shift for the programmer. You usually need to define a state variable and provide a set of input/output/accumulator functions and register all that as an aggregate function. This is because of the way this calling convention is meant to be integrated into query plans in a streaming fashion. It's a bit like forcing a programmer to learn some async or co-routine calling convention without offering a simpler synchronous option as a starting point. It's an obstacle. The application of window or ordered-set aggregate functions also require a lot of verbose syntax to wire up columns or expressions to function inputs and to configure the windows and ordering modes. There isn't really any support to abstract these details away behind the named function, such that the naive user could just call it without knowing how it works internally to solve the whole problem. In other words, having a functional abstraction which can encapsulate data-preconditioning behind an easier interface. Finally, this approach only addresses the case of reducing a set to a scalar or assigning new scalars for every row in a set. To match the generality of the numpy API would require functions that take sets as input and produce arbitrarily different sets as output. It also needs the host language to allow low-syntax manipulation of intermediate data when composing functions. So, for full generality, a data transform function might take one set as input and produce another set as output. We would also want things like: set-based function interface typing and function overloading; functions that can setup their own desired traversals of input data including sorting and filtering; functions that can allocate (potentially) large intermediate data using sets or other common data structure idioms; and complementary call-site syntax to easily compose functions with little syntactic boilerplate but also with low marginal syntax overhead to customize and restructure data passed between functions.
- kiernanmcgowan 4y agoViews, especially Materialized Views if you’re on Postgres, are about as close as you can come to a “one neat trick that solves all your problems” in modern software development imo.
- remus 4y agoI'd love to use materialised views more often but my experience is that they're full of annoying limitations, typically around functions that feel like they should be immutable but aren't.
- kiernanmcgowan 4y agoRight, I think the timestamp with timezone type is a great example of one of these short comings. I’ve generally used it as a step in a data pipeline, where some intermittent state or aggregation where being stale/stable for a period of time is ok. Did this recently for a daily trends report on some news data - live querying the data is slow, but baking it off into a MV makes it much faster and keeps the query result from changing too rapidly to new data.
- ttfkam 4y agoMaterialized views + pg_cron = chef's kiss for simple yet performant report generation.
- irrational 4y agoOh good, it’s not just me. As soon as I read his post I said to myself, “It sounds like he hasn’t heard of views.” But the first comment didn’t mention views and I was so confused that I started wondering if I misunderstood the post.
- viraptor 4y agoThere are situations where you can't use the views. For example I'm writing software which integrates with $vendor app. Not only do I have restricted select access, I have no influence on the schema at all. By definition, I have to send a complete query every time. And there are many similar scenarios.
- EMM_386 4y agoThey did mention they've only been using it 3 years. It's been an ANSI standard for 36 years.
- wodenokoto 4y agoI disagree. This is a problem with filtering data. Creating a view every time you want 1 row from each group based on some calculation is beyond cumbersome IMHO.
- tluyben2 4y agoBut OP says every single time in their post which nicely fits a view as building block for more readable and simpler sql. Their issue seems not a one of filtering problem.
- wodenokoto 4y agoYou can't build a view that allows you to do that query with an arbitrary function instead of MAX. It's _every single time_ you need to do this kind of trivial querying you have to write this convoluted mess of SQL.
- oedo 4y ago>with an arbitrary function instead of MAX Many systems support user-defined aggregates[0][1] [0] https://www.postgresql.org/docs/current/sql-createaggregate.html https://www.postgresql.org/docs/current/sql-createaggregate.... [1] https://docs.oracle.com/en/database/oracle/oracle-database/21/addci/using-user-defined-aggregate-functions.html https://docs.oracle.com/en/database/oracle/oracle-database/2...
- richbhanover 4y agoWhat do you think of PRQL ("prequel")? https://github.com/prql/prql https://github.com/prql/prql
- vassilevsky 4y agoCool name ;)
- euroderf 4y agoThe idea that you can visualize your pipeline and then "put it to paper" is pretty nice.
- mambru 4y agoRelevant discussion: https://news.ycombinator.com/item?id=30060784 https://news.ycombinator.com/item?id=30060784
- SQL2219 4y agoWith dynamic sql you can write your own. I know you don't have time, just throwing it out there.
- db48x 4y agoPeople build composable and reusable abstractions on top of sql all the time.
- quantified 4y agoExamples would help. It's an assertion people frequently make. I have struggled with homegrown not-particularly-good ones. One problem is finding a good set of abstractions for the things I really need to abstract on. So there are probably different abstractions to note. In the same vein that different languages provide higher-level abstractions for machine code or JVM bytecode.
- PaulHoule 4y agoStored procedures, user-defined functions, views…
- quantified 4y agoWe may have differences in how we define "abstract". Views and SPs encapsulate, but they don't change the difficulty of parameterizing on table name or column name within SQL, for example. No spread operator to say "select the columns from thing X that are going to be inserted into thing Y". There are so many different ways to abstract aspects for different goals.
- simonw 4y agoThe Django ORM is one of my favourite abstractions on top of SQL.
- PaulHoule 4y agoI am using JooQ now at work to write SQL statements in a Java DSL. Java’s type system mostly works as intended and is helpful, particularly in conjunction with an IDE. Many kinds of metaprogramming are possible with JooQ.
- PaulHoule 4y agoIn the semantic web, RDF can put every table in the universe in a unique namespace and OWL can bind together multiple tables and do inference at a high level, then you can query it with SPARQL which is quite similar to SQL at heart. The Department of Defense was asked by Congress to answer “Where does the money go?” and tried to use OWL and SPARQL queries across all the relational databases they owned but they couldn’t get it to work. I can’t help but think something along those lines could present as a ‘low code’ data tool. You can accomplish something similar with views, foreign tables, stored procedures, user defined functions, and other mechanisms which are common in databases like PostgreSQL, Microsoft SQL, etc. I find PostgreSQL pretty handy because it supports embedded data structures such as lists in columns, supports queries over JSON documents in columns, etc. My favorite DB for side projects is ArangoDB which uses collections of JSON objects like tables and the obvious algebra over them which lets you query and update like the best relational dbs but you don’t have the programmability of views, stored procedures, etc.
- shubb 4y agoI read this like "DoD tried to do quite a complicated semantic layer over tables project but failed because the RDF cluster of technologies is horrible. It would have been easier to code from scratch with SQL and python". I think you are pretty familiar with those technologies. Do you think they have any value?
- PaulHoule 4y agoI see the problem of the DoD being asked where the money goes as an intractable problem, not least because they have more than one reason that they don't want to tell you. That is, it will defeat any technology that you bring to the table. My own feelings about RDF are mixed. On one hand I've accomplished quite a few things with it, for instance, developing a technique for bringing multiple schema systems (say EMOF, XML Schema, and FIX Orchestra) into a single framework but it has been like https://supernatural.fandom.com/wiki/Binb%C5%8Dgami https://supernatural.fandom.com/wiki/Binb%C5%8Dgami for me in that I have put huge amounts of time and money into it with very little return, not because of technical problems per se but because of problems marketing it. There's a certain kind of stupidity inside the community that keeps it from making tools that are truly effective but there is even more stupidity outside the community that makes it seem it won't ever be understood. The semantic web seems to be cursed that it will never develop a brand like GraphQL, Docker, mongodb, or "FAANG" that works like a Jedi mind trick to bring people into its domains. For instance I saw GraphQL as beneath contempt for many years because there was no specification, it was just "Facebook will return any answers that further their business interests if and when they want to" and somehow people are seduced by that the same way that they are seduced by Donald Trump or will cut out their left eyeball because Matt Butts said that is how to raise your PageRank by 0.1% next Thursday.
- PaulHoule 4y agoThe one that drives me nuts is select this,count(*) from that group by this which is the most common data exploration query of them all which makes you type ‘this’ twice.
- thomasdziedzic 4y agoselect this, count(*) from that group by 1
- dominotw 4y agoI actually find this less readable. i have to go back and scan the select to see what column this index corresponds to. Totally breaks the flow of reading.
- eatonphil 4y agoTested this out on Postgres and it works: SELECT name, count(1) FROM (VALUES ('Kev',12), ('Meg', 14), ('Kev', 14)) people(name, age) GROUP BY 1 Produces: [[Kev, 2], [Meg, 1]]. But I don't get it. Could someone explain how this works?
- 4y ago
- simonw 4y agoI find CTEs (the WITH statement) greatly improved my ability to run more complex queries because they offer an abstraction I can use to name and compose queries together. SQL views are useful for that kind of thing too.
- warmwaffles 4y agoThe only issue I see with CTE's and have encountered, is poor query performance. The query planner needs to improve in this regard. At one point I noticed some of my CTE with queries being written to a temp file on disk and used to join against, when an inlined query did not do this producing the same results.
- srcreigh 4y agoIn Postgres you can use AS NIT MATERIALIZED to avoid the temp buffers. A recent PG version also makes this default, but only when the CTE is only queried once later. This can definitely cause issues. I’ve seen queries with CTEs that basically process unindexed 100 MB data due to querying the materialized rows. It would be cool to be able generate indexes with the CTEs though
- warmwaffles 4y agoHow would this look with a sample query? (using AS NIT MATERIALIZED)
- jdmichal 4y agoWITH one AS NOT MATERIALIZED ( SELECT 1 AS value, 'one' AS name ) SELECT * FROM one As srcreigh said, this is the default since I believe PostgreSQL 12, as long as the CTE is only used a single time. If you use it more than once, it by default wants to materialize it instead of recalculating each time. You can also force materialization by dropping the `NOT`. Also note that materialization acts as an optimization fence. That is, PostgreSQL will not push down filter criteria and such from the query into the `WITH` clause. It can do such if it's not materialized.
- SakeOfBrevity 4y agoCheck out dbt (not dbt-cloud), great open-source SQL tool, macro system is included in the ever-growing set of features. It helps managing SQLs a lot.
- sph 4y agoThe problem with SQL is that it's all about transforming data, and that would better be modelled as a pipeline. https://prql-lang.org/ https://prql-lang.org/
- CodesInChaos 4y agoYou could take a look at EdgeDB. It's built on postgres, but uses its own query language and data model. Its data model has build in support for links and polymorphism. The query language EdgeQL makes it easy to follow links and output embedded arrays.
- sequence7 4y agoAgreed, EdgeDB[0] looks to be trying to solve a lot of SQL's perceived flaws with EdgeQL[1]. It's not the first attempt and I suspect it won't be the last but composability is explicitly one of the challenges it addresses. [0] https://www.edgedb.com/ https://www.edgedb.com/ [1] https://www.edgedb.com/showcase/edgeql https://www.edgedb.com/showcase/edgeql
- bawolff 4y agoIt sounds like you just reinvented VIEWs?
- e12e 4y agoVIEWs and generated columns should be able to help with these problems? https://www.postgresql.org/docs/14/ddl-generated-columns.html https://www.postgresql.org/docs/14/ddl-generated-columns.htm... > A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables.
- RedShift1 4y agoUnfortunately with Postgres, generated columns are always stored on disk, so you lose some storage space.
- bawolff 4y agoFinally, something for mariadb fans to hold on to in the flame war.
- RedShift1 4y agoHaha I guess :-D. Though I have a workaround for complicated generated columns that I don't wish to store: using a function. So my select would look like: SELECT a, b, something_complicated(table) FROM table. Of course you have to remember to do that but hey it's something.
- srcreigh 4y agoMy reason for liking MariaDB is that MySQL uses a B-Tree for primary storage. In Postgres if you want a table with large keys, you have to store them twice once in the index once in primary storage (hash table). In MySQL there’s no hash table you basically just store the rows in an index. Could be as much as 40% reduction in storage… 400GB instead of 1TB. Postgres does have index compression as of a recent version, but I’m not sure how much that would improve random keys.
- tacosbane 4y agoI think every dialect has an idiomatic way to do what you're asking. e.g., in Snowflake it's `select * from sales_department_details qualify no_products_sold = max(no_products_sold) over (partition by sales_person_id)`, PSQL `select distinct on (sales_person_id) * from sales_department_details order by sales_person_id, no_products_sold desc`, ...
- hantusk 4y agoPostgres can do the"function over (partition by col_a order by col_b) as well. Agree, SQL can already do what the OP proposes in almost the exact same syntax. and together with CTEs, intermediate temp tables or views, you can totally avoid the spaghetti and break down the queries in manageable chunks.
- tacosbane 4y agopostgres has window functions but it does not have the `qualify` clause.
- ttfkam 4y agoTrue, but you can simulate it by running the query with the window function in a CTE and then make a separate SELECT with the appropriate WHERE clause. More verbose, but the same effect and result. Using NOT MATERIALIZED on the CTE can also potentially let the planner do better query optimization on it.
- pgt 4y agoLearn Datalog Today: http://www.learndatalogtoday.org/ http://www.learndatalogtoday.org/
- benjiweber 4y agocreate view max_products_sold as select sale_person_id, max(no_products_sold) AS no_products_sold from sales_department_details group by sale_person_id; select sale_person_id, sale_person_name, no_products_sold, commission_percentage, sales_department from sales_department_details natural join max_products_sold;
- anon84873628 4y agoYou sound like the exact target for Malloy, which was posted to HN recently: https://github.com/looker-open-source/malloy https://github.com/looker-open-source/malloy
- dasil003 4y agoAgreed that SQL lends itself to spaghetti. However after working with it for 25 years both as a developer but also collaborating with analysts and data scientists, I have to say I appreciate its basic declarative nature and remarkable portability as it has expanded into distributed databases. For me the value of being able to walk up to an unfamiliar database of widely varying tech and start getting value out of it immediately is the killer app. Macros or other DB-specific extensions would be useful at times, but I’m not sure how much they would enable solving the messiness problem in a generic way that reshapes the paradigm more broadly. My instinct is the messiness is a consequence of the direct utility and trying to make it nicer from a programmerly aesthetics point of view might be hard to sell. It’s not like SQL doesn’t have affordances for aesthetics already (eg. WITH clauses).
- fny 4y agoSQL is masquerades as declarative. It's relational algebra in disguise. This results in... - Queries that are difficult to interpret/verify. - Non trivial amount of effort to go from question to query: I often feel like I have to jump through hoops to obtain something trivial. I have to think, and I don't want to think.
- dasil003 4y agoI agree that SQL is needlessly baroque. Not sure whether I agree that there are too many hoops for trivial things though. Leaving SQL ergonomics aside for the moment, isn't relational algebra something which does require one to think carefully to avoid hastily producing plausible but incorrect queries?
- alliao 4y agoPowerBI is definitely down the "don't want to think" alley, lots of misleading graphs are born that way lol
- spaghettiToy 4y agoDisagree. Microsoft sells these as "unskilled", but at the end of the day, you still need skill but also perseverance. At least SQL is old enough to get plenty of relevant search results. You can be a copy-paste DJ if you really can't figure it out.
- scubbo 4y agoAbsolutely. https://www.scattered-thoughts.net/writing/against-sql https://www.scattered-thoughts.net/writing/against-sql (found from HN a week or so ago)
- default-kramer 4y agoYes, I can't believe that the awesome power of relational DBs is still hindered by the major flaws of SQL. Here is my attempt at something better: https://docs.racket-lang.org/plisqin/Read_Me_First.html https://docs.racket-lang.org/plisqin/Read_Me_First.html The feature I am most pleased with is that joins are values (or expressions, if you prefer) which can be then refactored using normal techniques.
- mamcx 4y agoCheck my project: https://tablam.org https://tablam.org --- Exist 2 major ways to solve this: You do a transpiler (like most ORM are, actually) or you go deeper and fix from the base. The second could yield much better results. It will sound weird at first, but not exist anything that block the idea of "a SQL" language work for make the full app, with UI and all that. SQL as-is is just too limited, in some unfortunate ways even for the use-case of have a restricted language usable for ad-hoc queries, but that is purely incidental. The relational model is much more expressive than array, functional models (because well, it can supersede/integrate both) and with some extra adjustment you can get something like python/ML that could become super-productive.
- sshine 4y agoPine [1] and jq [2] are combinator query languages. Every expression represents a filter of some kind. In SQL (and in relational algebra), every expression represents a dataset. That means composing SQL expressions is composing data, not composing operations on data. Since there is a somewhat low, backwards-compatible barrier to entry to build a combinator language on top of SQL (Pine is an example), whether or not trading the increased learning curve of using combinators for better composability is a good idea can stand the test of time. [1]: https://github.com/pine-lang/pine https://github.com/pine-lang/pine [2]: https://stedolan.github.io/jq/ https://stedolan.github.io/jq/
- trollied 4y ago>Frankly, it seems like some sort of macro system is needed Views, materialized views, CTEs.
- fny 4y agoNo, these are all highly coupled to the underlying data structure. Even stored procedures tend to know a bit too much. Say I have a table with the following properties: - It has a column that corresponds to an id - It has a column that corresponds to a category - It has a column that corresponds to a value - It has a column that corresponds to a timestamp - It has a bunch of other columns I want to have a general transformation that allows me to: Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max. The stored procedure ends up being a mashup of string concatenation. Meanwhile, it just feels something is missing to make the language expressive enough.
- zasdffaa 4y ago> No, these are all highly coupled to the underlying data structure Difficult to be sure what you mean, but I think you're wrong. You can select from any table source (edit: result set, if you prefer that term). > Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max. select min(category) as mincat, max(category) as maxcat, min(value) as minval, -- etc where ts between <start> and <end> I am not sure what you're asking for if the above is not it. Can you give an example please?
- setr 4y agoHe wants to do the same thing, but on 100 different tables. The only thing that changes is the tablename and column names involved. You can copy, paste and edit 100 times and create 100 view objects Or in eg numpy-python, you’d write a single function taking the table/columns as arguments, and reuse it. I think the usual SQL solution would be to implement a stored function and start smashing strings together
- TekMol 4y agoPut your example on https://www.db-fiddle.com/ https://www.db-fiddle.com/ or http://sqlfiddle.com/ http://sqlfiddle.com/ or some other sql fiddle then we can all experiment with it and come up with leaner solutions. Just asking for mooar build in stuff just leads to bloated software.
- alrlroipsp 4y ago> Frankly, it seems like some sort of macro system is needed. Check out stored procedures and views. Here's a great summary: https://stackoverflow.com/a/5195020 https://stackoverflow.com/a/5195020
- RedShift1 4y agoIf you make the joining keys have the same name, you can use USING(col_foo) instead of table1.col_foo = table2.col_foo. It's one of the reasons I always use tablename_id as primary key name and foreign key name. Doesn't always work (for example 2 foreign keys linking to the same table but represent something different like created_by and modified_by).
- michael_j_x 4y agoFeel you. I've long moved to jupyter notebooks as my de-facto database tool. I use python functions that generate the SQL, which I then execute in jupyter and load the results into pandas.
- mechanical_bear 4y agoI’m raising my eyebrow here as far I can. Care to share what sort of use case this is for? Seems…elaborate.
- hotdamnson 4y agoThis is a very poorly written SQL.. Try some modern syntax: select DISTINCT sale_person_id, sale_person_name, max(no_products_sold) over (partition by sale_person_id) AS max_products_sold, commission_percentage, sales_department from no_products_sold;
- greggyb 4y agoThere are a number of query languages to address this type of reuse and composability for analytical query workloads. - MDX: created by Microsoft to provide a dimensional query language. The language is incredibly powerful, but depends on your understanding of dimensional modeling (go read Kimball is the best starting point for learning MDX). There are several tools, both commercial and open source, which implement an MDX interface to data. - DAX: Microsoft's attempt to make MDX more approachable. A relational, functional language built on top of an in-memory columnstore relational storage engine, and used in several Microsoft products (Power BI, Analysis Services Tabular mode). - Qlik has its own expression language whose name I am not sure of. - Looker has LookML There are a lot of BI tools out there. Not all have an expression language to support reusable measures/calculations, but many do. You may want to look into one.
- fny 4y agoThanks! MDX and DAX look powerful but also unreasonably complicated. LookML seems even more constrained. I'll keep digging.
- greggyb 4y agoSuch languages offload more work onto the data model than SQL does.[0] For example, in the Tabular model (where you would use DAX), relationships are defined in the model (and supported by an in-memory data structure that you can shortcut-think of as a pre-computed hash-join) and implicit in (most) expressions you write. If you think of SQL as a general-purpose query language, these BI-centric languages are DSLs for analytical queries. They are most useful for workloads where you have many queries that all use the same data model and are reasonably served by a pre-defined set of calculations/metrics. [0] Do not mistake me, good data modeling is important for SQL as well.
- croes 4y agoWouldn't SELECT * FROM (SELECT DENSE_RANK() OVER ( PARTITION BY sd1.sale_person_id ORDER BY sd1.no_products_sold DESC) r ,sd1.sale_person_id ,sd1.sale_person_name ,sd1.no_products_sold ,sd1.commission_percentage ,sd1.sales_department FROM sales_department_details sd1) sd Where sd.r = 1 do the same without the inner join?
- rjh29 4y agoYes, you actually know SQL, unlike other people who don't even know window functions exist.
- fny 4y agoDENSE_RANK is not ANSI SQL, also you're implicitly performing a join with your "where" clause.
- trimethylpurine 4y agoWhich SQL engine supports none but ANSI key words? You're implicitly commenting in binary, but you didn't have to do any math.
- forinti 4y agoFrankly, the biggest problem with SQL is that not enough people know it well (and a lot of people think they know it well).
- jppope 4y agoRoughly would you consider your statement equal to "SQL is designed for experts?" just curious
- forinti 4y agoNot at all. I think it looks deceptively simple so programmers don't waste too much time learning it properly. But you can take up a good book (such as Joe Celko's) and set up a good foundation in a short time.
- aae42 4y agohe seems to have done a whole series, which would you recommend?
- forinti 4y ago"SQL for Smarties" is a good book to learn the language and "Puzzles and Answers" has a good collection of problems/solutions for typical situations you might find in real life.
- onetom 4y agoI happened to be in charge of organizing the translation of one of Celko's books to Hungarian. I even translated some chapters myself and proof-read the other chapters. It was indeed an eye-opening exercise! My background at that time was DBase, FoxPro and reading the Postgres docs. It was great to read don't recommendations about how to use the toolkit of SQL to answer real-world questions.
- Rodeoclash 4y agoExactly. Even knowing a small bit of SQL (i.e. enough to know the full scope of what SQL can give you so you grok the limits of your ability) is a super power compared to using ORMs. The right views, applied in the right places are like a super power compared to trying to do the same thing in application code.
- AtlasBarfed 4y ago1) templating constructs or env vars? The programming languages can provide that too, but... prepared statements and variable-based substitution was a huge improvement to SQL code from programming languages (at least in java and ruby). Why wasn't that part of the SQL standard, why did it have to evolve organically? If it was in the specification, so many SQL injection vulns would have been avoided. 2) SQL statement syntax should start with the table specification so you can get autocompletion help when doing the columns and other expressions. The INSERT statement has it right, while SELECTs suck because the columns are specified before the WHERE clause, so there is no context. At least provide alternate syntaxes like SELECT FROM <table> COLUMNS <cols> WHERE <whereclause>, it would be minimal for the DB vendors to support. 3) SQL standards have been too nice to vendors, especially Oracle. The lack of portable SQL between databases has hamstrung the industry. 4) Stored procedure standards should have been formalized, as should standard apis. JDBC/ODBC were ok, but why are programming languages doing major enhancements/de facto standards setting?
- carlineng 4y agoI've written about this topic a fair amount; first examining some of the criticisms of SQL that have been present since the language's inception [1], then looking at a new project called Malloy that I'm quite excited about, and think has a lot of potential to address some of the problems of SQL as it relates to data analysis [2]. [1]: https://carlineng.com/?postid=sql-critique#blog https://carlineng.com/?postid=sql-critique#blog [2]: https://carlineng.com/?postid=malloy-intro#blog https://carlineng.com/?postid=malloy-intro#blog
- shp0ngle 4y agoI think the main problem with SQL is how it tries to be too “human like” and “non-programmers friendly” with all the English words, when it’s still used by programmers in the end. I don’t see Malloy fixing it as it’s still using many English words and trying to be too much “sentence like”, but maybe I read it wrong
- Myrmornis 4y agoI agree that a major problem with SQL is its misguided attempt to be natural language-like. However skimming malloy examples it looks like they are just trying to be strongly declarative but not making the natural language mistake.
- carabiner 4y agoThere needs to be a Pandas to SQL translator.
- mr_toad 4y agoThere need to be more people who know both, so they aren’t trying to jam square pegs into round holes.
- cultofmetatron 4y agoElixir's Ecto library basically lets you write sql in elixir while abstracting away all the stuff op talking about. you can simply assign a subquery to a variable and reuse it. PS: a lot of you are saying use views. thats dangerous. In most cases, thats a nice way to end up with ballooning autovacume processes as your databasse gets bigger. Materialized views are fine as long as your use case doesn't need realtime accuracy.
- rawgabbit 4y agoWhat you described is already solved by window functions.
- kristov 4y agoI think it would be neat if columns of a resultset could contain other resultsets (relvars?) as values. This would be the natural outcome of a join operation, and give greater flexibility on how you want to reduce those sub-relvars down. It also makes recursive queries more natural, yielding a "tree" of results. You could even build a reducer for a recursive query that concatenated rows to a string to serialize into whatever: json, XML, etc.
- deergomoo 4y agoThis is essentially how EdgeDB’s “EdgeQL” language [0] works. Pretty much everything it deals with is a set. It maps much nicer onto many types of application code. [0] https://www.edgedb.com/ https://www.edgedb.com/
- x-shadowban 4y agoReally would like "select * except ..." and a non-dynamic-sql way to remap column names
- spullara 4y agoExcept in ad hoc queries you shouldn't be using * at all so it isn't really an interesting problem to solve.
- mr_toad 4y agoThat SQL has * as the only abstraction for column names is quite frustrating. I can think of so many other ways of dereferencing column names, from using a map/or lookup table to using types. For example, selecting the unique id of a table regardless of what its name is.
- spullara 4y agoSomething like that seems reasonable until you realize that the unique id of a table could be a composite key and then things get complicated.
- thangalin 4y agoFrom https://bitbucket.org/djarvis/rxm/src/master/ https://bitbucket.org/djarvis/rxm/src/master/ : root > people, # "root" keyword starts the document person > person, # maps table context to a node .age > @age, # @ maps a column to an attribute node .first_name > name/first, # maps a column to a node .last_name > .../last, # ... reuses the previous node's path account.person_id +> person.person_id, # +> performs an INNER JOIN account > account, # context is now "account" node .id > @id, # account id attribute ^, # pop stack to previous table context address > address, # switch context to "address" node .*, # glob remaining columns ; # Denotes optional WHERE clause The query produces: <people> <person age="42"> <name> <first>Charles</first> <last>Goldfarb</last> </name> <account id="123"/> <address> <id>456</id> <street>123 Query Lane</street> <city>San Francisco</city> </address> </person> </people> It wouldn't take much to make that a JSON document.
- spullara 4y agoI translated this into "I don't really know SQL but I think I do".
- SnowHill9902 4y agoThat’s because you are 1) writing naive SQL and 2) forcing naive SQL on an arbitrary data structure. You may use VIEWs, CTEs, GROUP BY CUBE/ROLLUP for 1) and rethink your structures for 2).
- throwyawayyyy 4y agoThe story of the past 10 years at my FAANG has been one of making our key-value databases look as much like relational databases as possible, complete with SQL query engines (at least two of them, because what would a FAANG be without multiple slightly incompatible ways of doing things?). Honestly it's been a huge boon. Should we have abandoned SQL entirely, and come up with a new language? Maybe. But given that the primary initial motivation for this work was to allow analysts to creation millions of reports without bothering eng, it had to be a language that analysts understand. That's SQL.
- wodenokoto 4y agoBigQuery? I did appreciate the SQL interface, but as poster said, thinks could be a lot better, especially for exploratory analysis.
- taf2 4y agoAre there any good libraries for exposing a SQL interface to specific languages that FAANG could open source and share making easier for more of us to do the same?
- code_biologist 4y agoTrino (nee Presto) and Hive both have open source Java SQL parsers. I'm sure their connection management code would also be really strong place to start for a full SQL server interface. That said, it almost certainly is a better idea to write a connector to something like Trino and just build on its capabilities rather than write your own SQL interface.
- deleted 4y ago[deleted]
- isthisthingon99 4y agoViews are only useful if you will repeatedly do the same query on the same view. In this case, it sounds like the OP was saying for every pair of tables like this you have to do this big query. Still, views are free aren't they? Might as well create a view even if it is just for one query.
- george_ciobanu 4y agoCheck out https://human.software https://human.software, a visual language replacement (no code)
- temp_account_32 4y agoSQL does seem antiquated, I know a lot of people who have worked with it for ages are in the 'don't fix if it ain't broken' camp but consider even completely basic things that you cannot do, such as get autocomplete for what you want to select. Because you are forced to specify what you want to SELECT before the FROM, you are left guessing the column names. In contrast, in a programming language or ORM, you'd do something like (pseudocode) Entity.Select( ... ) and can get suggestions of the fields, but with SQL you are forced to do it backwards.
- worthless-trash 4y ago> such as get autocomplete for what you want to select. By "autocomplete" do you mean like column/table names ?
- temp_account_32 4y agoYeah, column names. For me, it would make much more sense to start the statement with FROM instead of SELECT, to let whatever client you're using know which table you're selecting from, and then it can help you select the columns. Hope I explained this well enough.
- worthless-trash 4y agoI'm wondering how this could be done in an editor. I guess if you could pump it into a LSP, maybe it could have knowledge of the tables/views/etc database internals.. Some people put these inside strings though.. Like this: https://www.w3schools.com/php/php_mysql_select.asp https://www.w3schools.com/php/php_mysql_select.asp (good practice arguments aside), Its a non trivial problem to solve across all languages.
- gerdesj 4y agoI'm not an expert but whenever I need to use SQL it seems to be complicated enough as I need at the time to do the job. When the job becomes more complicated, so does SQL. I think it is a remarkably well designed language. I often get decent results for minimal effort. It isn't something I use day to day but more often month to month and I generally forget what I learned a couple of years ago. For example, I wanted to clear down some records for a defunct client from our backup results database. I started by using SELECT to reliably find the customer (not as simple as it might sound). Then I construct an INNER JOIN (in this case most joins are the same) to link to the jobs. A SELECT COUNT(*) experiment looks about right. I snapshot the VM and change my experiment to a DELETE. SQL is a bit strange but it lends itself to experimentation ... to results, way better than any other language I know. Then you get into functions and views and the like. SQL has just enough complication for whatever job you have at hand and no more. I recently ran up a query in Icinga2 (Director) against a MariaDB and it seemed to write itself. I get far fewer syntax errors in SQL than anything else that I try to abuse!
- rzwitserloot 4y agoThis question breaks down across 3 completely different lines. But first, SQL is a descriptive language: You describe what you want, you don't describe how you want it. Instead, you rely on extremely complicated optimisers and a lot of using `EXPLAIN` to see what's happening under the hood. This is fundamental in the very _point_ of SQL. Keeping that in mind, what do you mean: [1] You feel the general notion of doing relational queries using a descriptive language is just broken. In which case, the fact that so many 'simpler' systems decided to add SQL support instead of just relying on the fact that folks will program their way into efficient queries is telling. People like being able to do this. [2] You think the general notion is great, just, SQL specifically is badly designed. This then breaks down into two explanations. [2a] You haven't actually read the spec, specifically your DB engine's spec. There's a ton DB engines can do - you can make your own procedures, your own aggregator functions, add triggers, and use VIEWs to combine it all (and VIEWs can act entirely as tables, including letting you INSERT or UPDATE them). For example, I'm pretty sure you can do precisely what you want, or at least get quite close, by only adding a little bit of extra definitions in postgresql. Either you just don't know enough (I'm just guessing here, based on very little info, please don't take it personally), or you do, but you find all that 'needlessly complicated'. In which case I think you're just misjudging how varied (and therefore complicated) your average DB user's needs are. Add the ability to do everything you want, but then for all use cases of people like you, and it is so complicated, you yourself would say 'yeah but not so complicated', and we're right back where we started. [2b] No you really do know your way around SQL, including all the various exotic features that DB engines, particularly really good ones like psql, offer. And it's not good enough and you think you have a good grasp on how to make SQL specifically better. In which case: That has been done. Multiple times. The XKCD with the 14 standards comes to mind. Here is a REALLY long list of query languages: https://en.wikipedia.org/wiki/Query_language https://en.wikipedia.org/wiki/Query_language - pick your poison. But first, think about the fact you didn't know about any of these. Whatever you're planning to 'replace' or 'fix' SQL, isn't that just doomed to be the 19th entry on that wikipedia list, just as forgotten as all the others? In which case, [A] many, many db engines have extra syntax you might want to look at. You can make recursive queries, windowing functions, define your own procedures, and more - and use VIEWs to let you do precisely what you want. I'm pretty sure you can do precisely what you desire with postgres, using only a fairly simplistic amount of
- wilde 4y ago> Fundamentally, SQL lacks meaningful abstraction, and there's no sane way to package very common operations. Tables are the unit of abstraction. You’re on the right track with your instinct that you should be able to pull repeated work into a reusable unit. In many DB systems you can just register these sub queries as views and treat them as real tables. In the ETL world that I’m typically working in, you go one step further and just write pipelines to make useful intermediate tables. Reuse tables, not subqueries.
- mr_toad 4y ago> Frankly, it seems like some sort of macro system is needed. Like dynamic SQL? That has its own problems. Rather than trying to extend SQL I think that people should stop trying to use SQL/RDBMSs for every task.
- onetom 4y agoSQL was meant to be a hosted language from the get-go though, so any kind of macro-like feature was expected to be done in the host language. Of course it's a bit painful and error prone, given SQL is a textual language, but as HoneySQL (https://github.com/seancorfield/honeysql https://github.com/seancorfield/honeysql) shows, you can represent SQL statements as data and generate them programmatically in a safe manner. I think it's compatible with https://babashka.org/ https://babashka.org/ so you don't need a full Java environment to use it.
- onetom 4y agoSQL is great, but you do have to dig deeper and use more of its features, like many other comments mentioned, like WITH & VIEWs. On the other hand, maybe the time is ripe for you to "graduate" to using Datalog via other kind of databases, like Datomic, XTDB (fka Crux) and the likes (DataScript, Datalevin, etc) Of course knowing Datalog won't help you with your SQL issues, but you can look into changing jobs, if you like what you see or maybe do some ETL job to spill over some data into the mentioned DBs and see whether querying it with Datalog is any better.
- bandushrew 4y agoYou can use stored procs and/or views to hide the details of queries if you want. the specifics of an inner join vs outer join vs left join etc are crucial though. It makes sense to be able to specify them.
- Liron 4y agoI agree 100%. This post from the EdgeDB team makes a great case why SQL is outdated [1] [1] https://www.edgedb.com/blog/we-can-do-better-than-sql https://www.edgedb.com/blog/we-can-do-better-than-sql
- nknealk 4y agoThere’s a LAST_VALUE statement for this kind of thing that most databases support. Partition by sale person name order by number of products.
- heavyset_go 4y agoI agree that additional abstraction and convenience features would be nice to have in SQL. I disagree that it needs "help", which suggests that there is something fundamentally wrong with SQL that needs to change, possibly with help from outside of the SQL community. On the contrary, I think SQL is great, but it could always be better.
- CodeWriter23 4y agoNo wonder you think it needs help if you write it like that in one line with no indentation and insufficient capitalization.
- deleted 4y ago[deleted]
- truth_seeker 4y agoSQL is 4th generation programming language and turing complete. It is developer who needs help by knowing more of the inbuilt features and keywords of SQL and how to compose them in effective manner.
- jasfi 4y agoProcedural SQL is how you package reusable/common operations in the DB world. In terms of programming languages, you'd use an ORM. SQL isn't as broken as you think, it does what it's supposed to do very well. There are many ways to work with SQL such as procedural SQL and ORMs that build on it.
- wodenokoto 4y agoNicer formatting of OPs woes: SELECT sd1.sale_person_id, sd1.sale_person_name, sd1.no_products_sold, sd1.commission_percentage, sd1.sales_department FROM sales_department_details sd1 INNER JOIN ( SELECT sale_person_id, MAX(no_products_sold) AS no_products_sold FROM sales_department_details GROUP BY sale_person_id ) sd2 ON sd1.sale_person_id = sd2.sale_person_id AND sd1.no_products_sold = sd2.no_products_sold;
- wodenokoto 4y agoWith fear of syntax errors, I propose the following: WITH max_sold_per_id AS ( SELECT sale_person_id, MAX(no_products_sold) AS no_products_sold FROM sales_department_details GROUP BY sale_person_id ) SELECT sd1.sale_person_id, sd1.sale_person_name, sd1.no_products_sold, sd1.commission_percentage, sd1.sales_department FROM sales_department_details AS sd1 INNER JOIN max_sold_per AS sd2 ON sd1.sale_person_id = sd2.sale_person_id AND sd1.no_products_sold = sd2.no_products_sold; Which still isn't nice to "bang out" in an exploratory analysis. Especially because the thinking goes "Get the max sales numbers and associated IDs, and then add the meta data", but the code is still "with the meta data, join the max sales data" regardless whether you prepare the max sales data "at the top" (which I did) or in the join (as OP did) In dplyr, which imho has a nice syntax (but tons of other problems) it would be: sales_department_details %>% group_by(sale_person_id) %>% filter(no_products_sold == max(no_products_sold)) %>% select( sale_person_id, sale_person_name, no_products_sold, commission_percentage, sales_department ) First we group by ID, then on each group, we filter for the maximum sales, and finally we select the columns we are interested in. Super easy to bang out (and probably inefficient as we are moving tons of unnecessary columns between statements, but that is not the syntax fault)
- jhoelzel 4y agoFrom my own experience, SQL is solid. The problem that is see the most is the one we are taught in programming 101: atomic data. The information you seek above is computed and therefore justifies a view table instead of a "real one", because you really are doing something "complex". after that you can always select yourdata from yourview and you should be fine. Relational data can only be really efficient if its relational and sometimes, computed tables are also a nice thing to have but even better: sometimes a porgramm needs to aggregate the data periodically and put it into the database relationally.
- narrator 4y agoGenerally, I find that if you do anything too complicated with SQL it won't be scalable because the optimizer will start making bad decisions and you should just do it in the app tier where you can tune the algorithm more easily. Thus, if you need abstraction, you probably are doing it wrong already.
- qwerty456127 4y agoIsn't SQL a nice target for a no-code visual language to compile to? I also don't understand how does "SQL lacks meaningful abstraction". Most of serious RDBMSes have user-defined functions and stored procedures, that's just the ORM enthusiasts who are unwilling to use them.
- jonnypotty 4y agoI wish programming was easier sometimes too.
- avereveard 4y agoUse composition, views are great for that, modern databases will optimize trough views as well so it's not like the day of old where a view with a join was an all consuming monster. We already had the best minds in the world to try and replace sql, but because sql is rooted in group theory and math is hard to budge they all eventually had to get back and implement the group bys and havings and whatnot that complicate the sql syntax, except tackled on and with their proprietary syntax.
- wodenokoto 4y agoSELECT sale_person_id, max(no_products_sold) as max_sold, link(sale_person_name, max_sold), FROM sales_department_details I'm not sure what `link` is supposed to be, but wouldn't the dream syntax modification to SQL simply be SELECT sale_person_id, no_products_sold as max_sold, sale_person_name, commission_percentage, sales_department, FROM sales_department_details GROUP BY sale_person_id WHERE no_products_sold = max(no_products_sold)