Hacker News Reader

The biggest advantage of the terse symbolic style for me comes when you wish to rapidly compare different ways (algorithms) to compute the same function. For instance, Kadane's Algorithm usually takes a dozen+ lines in an algoly language, which would normally be presented in some kind of syntax-coloured code block external to descriptive paragraphs; it takes a dozen- characters in an array language, and therefore variations can be included inline to text describing the alternatives.

https://www.timestored.com/b/kdb-qsql-query-vs-sql/ How many database tables have a date time column and a natural ordering? Most the data I look at. Which makes it crazy that sql is based on unordered sets.

Thanks for the link I think this is a really interesting example:

> In qSQL this is: aj[`sym`time; t; q], which means perform an asof-join on t, looking up the nearest match from table q based on the sym and time column.

> In standard SQL, again you’ll have difficulty: sql nearest date, sql closest date even just the closest lesser date isn’t elegant. One solution would be:

> WITH cte AS (SELECT t.sym, t.time, q.bid, ROW_NUMBER() OVER (PARTITION BY t.ID, t.time ORDER BY ABS(DATEDIFF(dd, t.time, p.time))) AS rowNum FROM t LEFT JOIN q ON t.sym = q.sym) SELECT sym,time,bid FROM cte WHERE rowNum = 1

> It’s worth pointing out this is one of the queries that is typically extremely slow (minutes) on row-oriented databases compared to column-oriented databases (at most a few seconds).

This is a really nice example but I think it’s more about this as of join being a really useful operation, it appears both pandas https://pandas.pydata.org/docs/reference/api/pandas.merge_as... And duckdb https://duckdb.org/docs/guides/sql_features/asof_join.html

Pandas:

> pd.merge_asof(t, q, on='time', by='sym', direction='backward')

Duckdb:

> SELECT t.sym, t.time, q.value FROM t ASOF JOIN q ON t.sym = q.sym AND t.time >= q.time;

So it seems more like this is benefitting from a useful feature of time series databases rather than the features of an APL-family language.

Personally I find the pandas syntax to be the most straightforward here.

Yeah I can see the benefit of pandas for those reading the code. I think they learnt from kdb that both backwards and forwards was needed. Kdb had to create a new aj0 function. But your point is valid that kdb may be over optimising for first writing code.

To someone unfamiliar with the pandas api I think the SQL version is by far the most readable.

It's just a regular join with the `asof` keyword in front.

The pandas version assumes both fields have the same name, it separates the on and by conditions in a way that's not necessary in SQL version, and imo the "backwards" keyword is a bit ugly and error prone.

There was a step-change improvement for me when I tried expressing some JS patterns via `underscore.js` instead of procedurally: eg: http://underscorejs.org/#each

Thinking of something as `each | map | filter | sum` is waaay less buggy than writing bespoke procedural code to do the same thing. No doubt there is a "cost" to it as well, but the _abstraction_ is valuable.

Now, if there were a "compiler" which could optimize that whole pipeline down and squeeze out the inefficiencies between steps because it could "see" the whole program at the same time. (oh, I don't know, something like `SELECT * FROM foo WHERE a > 100 AND b < 9000 LEFT JOIN bar ON ...etc...`)

...perhaps you could get both an expressivity gain (by using higher level concepts than "for" and "while"), a reduction in bugs (because you're not re-implementing basic work-a-day procedures), and an improvement in efficiency (because the "compiler" can let you express things "verbosely", while it sorts out the details of efficiency gains that would be tenuous to express and keep up to date by hand).

I 100% agree, I think the functional features that have been added across all the popular languages (map, reduce, fold etc.) has been a positive. Nothing demonstrates it better (imo) than purrr in R: https://github.com/rstudio/cheatsheets/blob/main/purrr.pdf

I also think there is some merit to “high syntactical density” clearly if you can see the entire code in one place instead of having to navigate through many files or sections that’s beneficial. (Heavily discussed in the last big HN thread: https://news.ycombinator.com/item?id=38981639)

I also think JQ has proven the merit of tacit functional languages in that you can concisely write arbitrary transforms/queries on json that can be more expressive than SQL (many SQL engines have added JSONPath anyway). And I also think postfix is great for processing pipelines.

But I am not totally convinced in the approach of APL/J/Q/KDB for the combination of terse style + prefix + tacit because it makes the code so difficult to read. I think if you took an approach similar to JQ where instead of relying on symbols operators were just human readable words it would be easier to get us half way there to trying out the verb, adverbs etc. approach of the APL family. The problem with making it human readable text is that you lose the conciseness which is part of the draw of the APL family as they want to have a high syntax density and analogous code to mathematical expressions.

That's kind of what we're aiming for with PRQL (prql-lang.org). While currently it only supports SQL backends, I want to generalize that to things like underscore.js.

If I may be so bold as a pitch for adoption:

For Python, support `prql-to-sqlite3` (in memory?) "out of the box"

For JavaScript, support "array-of-dicts/objects" with no ceremony.

Basically, in python I could justify the depends if I can say:

    sql = prql.parse( """select * ...""" )
    results = db.execute(sql)

In JS, similarly:

    all_my_records = [ { ... }, ... ]
    function oh_god_why_nevermind(...) { ... }
    subset = prql.execute(
      `select * from all_my_records ...`
    )

(butchering all syntax, of course)

...basically "nobody wants to learn sql nowadays", and in javascript "my kingdom for a left-join!"

Pitch it as "graphql for data" (/zing!)

Thank you for the suggestion. I actually very much agree with you.

I've recently been updating my stack in other areas and have been utilising "Getting Started" or "Quick Start" sections a lot and thought that we're actually not making that easy enough or easy at all.

I'll look into incorporating your suggestion soon.

If you're up to it, you can open an issue in our repo and then I can ping you when that's done.