Hacker News Reader

Comment by vendiddy | Hacker News Reader

I think these are great suggestions.

It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL.

2 & 3 are my biggest pain points. I can't just extract functions like I can with a regular programming language. Instead, SQL queries get increasingly complex with no great tools to manage that.

For 3, products like https://materialize.com/ look interesting for being able to create derived materialized views that can efficiently be kept up to date.

View on HN

I'm not familiar with Ecto, dplyr, or DBT, but I would love an ML-like language to replace SQL. I'm imagining being able to pass a table (or any table-oriented data, like a sub-query) to functions that would type-check columns and would return table-oriented or scalar data. I'm not sure if this is actually possible in practice, but one can dream.

For instance, a "top 10" function that could be re-used on any table (apologies for my pseudo types and code):

  selectTop10 : Column -> Table -> Table
  selectTop10 orderByColumn table =
    SQL.selectAllFrom table
    |> orderDescBy orderByColumn
    |> limit 10

  limit : Int -> Table -> Table
  limit n rows =
    SQL.limitBy n rows

  orderDescBy : Column -> Table -> Table
  orderDescBy orderByColumn rows =
    SQL.orderBy [orderByColumn] SQL.Ordering.Desc rows

Not 100% sure about what you're suggesting, but wouldn't it be easier to pass your functions to your table/sub-query?

And that's exactly what you're able to do in most of the modern data warehouse services such as Snowflake. Inferences can be contained within internal/external user defined functions.

This is very reminiscent of made the big-data/map-reduce movement so notable, sending your query to the data instead of moving your data to the query. Sending your model to the data, instead of sending the data to the model.

I think I see what you're saying, and in my idea I'm suggesting the same - sending the function(s) to the data.

I don't quite follow what you are saying. In some SQL engines you can use row_number() function and derived tables (or CTE) to get top 10.

  SELECT Id,col1,col2
  FROM
  (
    SELECT 
    Id
    ,col1
    ,col2 
    ,row_number () over (partition by columnkey1, columnkey2 order by anycolumnwilldohere desc) as _row
    FROM _table
  ) as anytablealiaswilldohere
  WHERE _row <=10

My idea is you wouldn't have to cut and paste implementations like this. You would be able to just call the function with a column name and table name.

I'm currently doing work that uses Elixir's Ecto which goes a great deal towards what I think you're aiming at. I can write my SQL in a familiar, yet functional and composable style; while knowing what SQL will be produced in the end. Ecto, as I understand it, was inspired by Microsoft's/c# LINQ. I've not worked with that, but heard similar praises for that as exists with Ecto.

I'm saying this as with most of my experience being in SQL.

> It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL.

Not exactly but sort of this:

http://blog.hydromatic.net/2020/02/25/morel-a-functional-lan...

"someone could design a functional-style programming language that compiles to SQL". See e.g. R dplyr

DBT solves 2 & 3

Reply on news.ycombinator.com