Show HN: It's been nearly 50 years. Is SQL outdated?
https://github.com/SPLWare/esProc
Actually, the Q in SQL represents query. The original purpose of inventing SQL is to query (i.e., calculation), which is the main goal of SQL. However, it is hard to say that SQL is very competent when describing computing tasks. Here is an example that is actually not very complicated: calculate the maximum consecutive days that a stock keeps rising. Write it in SQL is like this:select max (consecutive_day) from (select count(*) (consecutive_day from (select sum(rise_mark) over(order by trade_date) days_no_gain from (select trade_date, case when closing_price>lag(closing_price) over(order by trade_date) then 0 else 1 END rise_mark from stock_price ) ) group by days_no_gain) The working principle of this statement won’t be explained here, it's a little confusing anyway. You can try it yourself. Then, why these two aspects cannot be well achieved in SQL? The reason why coding is difficult is largely due to algebra. Relational algebra has been invented for fifty years. The difference between the application requirements and hardware environments of fifty years ago and today is very huge. Continuing to apply the theory of fifty years ago to solve today's problems, does it sound too outdated? However, this is the reality. Due to the large number of existing users and the lack of mature new technologies, SQL, based on relational algebra, is still the most important database language today. Although some improvements have been made in recent decades, the foundation has not changed. In the face of contemporary complex requirements and hardware environments, it is reasonable that SQL is incompetent.
And, unfortunately, this problem is at the theoretical level, and it won't help no matter how optimized it is in practice, it can only be improved in a limited way, not eradicated. Regrettably, most database developers do not think of this level, or, in order to take care of the compatibility of existing users, they do not intend to think about this level. As a result, the mainstream database industry has been going around in circles in this limited space. Now then, how to make the calculation Easier in Writing and Faster in Running?
Invent new algebra! An algebra with “multiplication”, and then design a new language based on the new algebra.
This is where SPL comes from. Its theoretical basis is no longer the relational algebra, but something called discrete dataset. The formal language designed based on this new algebra is named SPL (structured process language). Innovations against the shortcomings of SQL have been made to SPL (more precisely, innovations against various deficiencies of relational algebra have been made to the discrete dataset). SPL redefines and extends many operations of structured data, specifically, it adds the discreteness, enhances ordered computation, implements a thorough set orientation, supports object references, and advocates stepwise operation. Recoding the previous problems in SPL will give you a direct feeling.
Calculate the maximum consecutive days that a stock keeps rising:
stock_price.sort(trade_date).group@i(closing_price<closing_price[-1]).max(~.len()) Although the calculation idea is the same as the previous SQL, it is much easier to express and no longer confusing, because of the introduction of ordering characteristic.
Take the top 10 out of 100 million pieces of data:
T.groups(;top(-10,x)) SPL has richer set data types, it is easy to describe the efficient algorithm that implements simple aggregation on a single traversal, without involving big sorting action.
Due to space limitations, we will not introduce SPL (discrete dataset) in an all-round way here, If you want to learn more about SPL, please head over to github and leave a comment if you have any questions.
Those who ignore the lessons of the past are doomed to reimplement them… poorly.
Your first example is what MATCH RECOGNIZE is for, no? https://modern-sql.com/feature/match_recognize
Also, you could use CTEs to make your SQL example far more readable/maintainable. Dumping subselects everywhere doesn't really sell your case; it just makes your argument against SQL weaker right out of the gate.
And on that note, why is it that folks always properly format and indent their code unless it's SQL? Yeah, if you write a statement with 150 characters in any language without formatting and indentation, it will look like crap. So why write your SQL with absolutely no efforts toward formatting and indentation unless you're intentionally trying to put your thumb on the scale? (On HN, you can preformat code by indenting four spaces.)
The final bit that appears to be missed is that SQL DML, by design, doesn't specify HOW to retrieve the data, only WHAT data you want returned. This library is (a) intimately tied to the JVM, so immediately not language agnostic, and (b) straddling that line for HOW to get the data. In exchange for personal preferences on syntax and their aesthetics, you've lost declarative aspects of querying, no matter how cherry-picked the examples are.
(C'mon! Be honest! How often does your example come up in real world development? I'm glad MATCH RECOGNIZE is making its way into the SQL toolbox, but it's not anything I'd be reaching for on a daily or even monthly basis.)
SQL:
WITH TT AS (SELECT RANK() OVER(PARTITION BY uid ORDER BY logtime DESC) rk, T.* FROM t_loginT) SELECT uid,(SELECT TT. logtime FROM TT where TT.uid=TTT. uid and TT.rk=1) -(SELET TT. logtim FROM TT WHERE TT.uid=TTT. uid and TT.rk=2) interval FROM t_loginTTTT GROUP BY uid
SPL: =t_login.groups(uid;top(2,-logtime)).new(uid,#2(1).logtime-#2(2).logtime:interval)
》example 2:Calculate the moving average of sales for each month before and after each month
SQL:
WITH B AS (SELECT LAG(amount) OVER (ORDER BY smonth) f1, LEAD(amount) OVER (ORDER BY smonth) f2, A.* FROM orders A) SELECT smonth,amount, (NVL(f1,0)+NVL(f2,0)+amount)/(DECODE(f1,NULLl,0,1)+DECODE(f2,NULL,0,1)+1) moving_average FROM B
SPL: =orders.sort(smonth).derive(amount[-1,1].avg()):moving_average)
Complex SQL is not something that everyone encounters every day, and the financial industry often has more. And MATCH_ RECOGNIZE currently does not have the functionality of all databases, and we cannot require everyone to use such databases. Furthermore, simple grammar is only superficial, and the new algebra will actually bring performance improvements. For example, using ordered grouping instead of hash grouping, using merge instead of hash when associating, and so on.
Again, there appears to be disconnect with regard to SQL. SQL does not specify ordered vs hash grouping nor merge instead of hash. That is an engine implementation detail and only touched upon in DDL (schema declaration).
The query side of SQL (DML) does not specify index or grouping strategies. At all. One bit. By design.
Got a reference to a table on a different server—or even a different geographic location? Doesn't matter. The SQL query doesn't change. Local or remote, physical or virtual, reactive or static, the query language doesn't change.
SQL has a number of advantages over other database languages, including:
It is a mature and well-supported language. It is easy to learn and use. It is flexible and scalable. It is supported by a wide range of database vendors. While there are other database languages and technologies that have emerged in recent years, such as NoSQL and cloud-based databases, SQL remains an essential tool for data management.