Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL: One of the most valuable skills

craigkerstiens.com

Post reply on HN
Print view View original

121–130 of 390 posts

Re: SQL: One of the most valuable skills

#121

Post by chucky_z »

teej wrote: post #110

For those trying to up their SQL game from basic selects and grouping, I suggest learning: - Common Table Expressions (CTEs). They will equip you to write clean, organized, and expressive SQL. - CREATE TABLE AS SELECT. This command powers SQL-driven data transformation. - Window functions, particularly SUM, LEAD, LAG, and ROW_NUMBER. These enable complex calculations without messy self-joins. Learning those three wil…

ROW_NUMBER is absolutely the best, especially for marketing related customer data.

Re: SQL: One of the most valuable skills

#122

Post by [deleted] »

kamaal wrote: post #113
dominotw wrote: post #102

Earlier quoted context omitted.

> It's nearly 5 decades old 5 decade old sql is nothing like modern sql with tons of proprietary extensions, partition, windows, collations, typecast, json and god knows what else. Your examples " Hive, Presto, KSQL, etc" are a proof of this, they are so vastly different from each other you cannot simply learn "sql" and expect to use those tools in any serious manner. This is precisely the proof of opposite that sql…

It would be insane to expect somebody arrive at a perfect feature full working version of anything in the first try. That's true for any piece of software. SQL is not done yet, the fact that you can put in neat little features still into it is a proof to how well it was designed. Heck we still have Lisp evolving today. A lot of things got done right in the early days.

[deleted]

Re: SQL: One of the most valuable skills

#123

Post by chillacy »

dominotw wrote: post #102
slap_shot wrote: post #67

SQL is one the most amazing concepts I've ever experienced. It's nearly 5 decades old and there is no sign of a replacement. We've created countless other technologies to store and process data, and we always seem to try to re-create SQL in those technologies (e.g. Hive, Presto, KSQL, etc). I run a early stage company that builds analytics infrastructure for companies. We are betting very heavily on SQL, and Craigs p…

> It's nearly 5 decades old 5 decade old sql is nothing like modern sql with tons of proprietary extensions, partition, windows, collations, typecast, json and god knows what else. Your examples " Hive, Presto, KSQL, etc" are a proof of this, they are so vastly different from each other you cannot simply learn "sql" and expect to use those tools in any serious manner. This is precisely the proof of opposite that sql…

I understood when OP said the "concept of SQL" that they referred to a pretty broad idea of querying tabular data in rows and columns with some structured language.

Re: SQL: One of the most valuable skills

#124

Post by dominotw »

chillacy wrote: post #123
dominotw wrote: post #102

Earlier quoted context omitted.

> It's nearly 5 decades old 5 decade old sql is nothing like modern sql with tons of proprietary extensions, partition, windows, collations, typecast, json and god knows what else. Your examples " Hive, Presto, KSQL, etc" are a proof of this, they are so vastly different from each other you cannot simply learn "sql" and expect to use those tools in any serious manner. This is precisely the proof of opposite that sql…

I understood when OP said the "concept of SQL" that they referred to a pretty broad idea of querying tabular data in rows and columns with some structured language.

I find it hard to accept that ppl are talking about 'querying tabular data in rows and columns with some structured language' when they talk about sql. They are surely talking about the specific syntax.

https://en.wikipedia.org/wiki/SQL

Re: SQL: One of the most valuable skills

#125

Post by busterarm »

zzzcpan wrote: post #106
busterarm wrote: post #98

Earlier quoted context omitted.

SQL is just fine. If incrementing a view counter, giving a one star review or inserting a log entry is important to you, you absolutely want that wrapped in a transaction. If you don't think so now, something will force you to think so at some point in the future. If you wait that long, it's probably too late. The direction this line of thinking trends in is not thinking or caring about what data you're storing or ca…

> If incrementing a view counter, giving a one star review or inserting a log entry is important to you, you absolutely want that wrapped in a transaction. You absolutely don't. You want it as CRDT operations, not transactions.

That still depends. You're still making a choice of priorities. You're making a choice that you're okay losing some data when failures happen.

For the mentioned use cases CRDT is probably an okay choice, but not always. In some use cases/industries, losing log lines or having inaccurate counts is absolutely not okay. There can even be regulatory concerns here. And fines!

It's also possible to scale traditional databases out pretty far. It's difficult and expensive, but in probably 98% of cases you can do it. If your company has to, it should be able to afford it anyway.

Re: SQL: One of the most valuable skills

#126

Post by eleclady »

conception wrote: post #82
kumarvvr wrote: post #76

SQL is a mind bender for me. I do a lot of work on Data, use python and pandas to do a lot of data magic, but the problem with me is my mind is too procedural in thinking. - Step 1 - Step 2 - Loop through results in Step 2 - Curate and finish output. I try very hard to transform the above steps into an SQL statement spanning multiple tables, but always fail and I usually fallback to python for manually extracting and…

I think what your are doing is fine. SQL is strongest at answering questions not processing data. I think metabase has the right approach: What question do you want to ask your data? If you want to process and transform your data, I think your tools you are using are great for that.

Not trying to play word games, but what is the difference between answering a question and processing data? Aren't they effectively the same?

Using another tool for processing data often results in recreating SQL mechanics at application level. E.g. select this data, retrieve it, loop and if this, then set that, etc. SQL does it way better, guaranteed.

Of course that's often required for technical reasons (scalability etc.) or processing that's too complex to implement at data layer, or just for cleaner design.

But SQL is amazing at processing data!

Re: SQL: One of the most valuable skills

#127

Post by truth_seeker »

Hell Yes. Although it takes some time to switch from Procedural or OOP to highly Declarative world of SQL, but man, its worth pursuing. Only thing which really helped me build my confidence was solving more real world problems which involves 2NF and 3NF design, JOINs, Triggers, Indexing, Views & Materialized Views for denormalization, CTE and Recursive CTE's.

You may call me an extremist but from server side programming point of view with Postgres and FDW (Foreign Data Wrappers) which has ton of features other than SQL only thing i miss is HTTP server. :)

Re: SQL: One of the most valuable skills

#128

Post by bigger_cheese »

kumarvvr wrote: post #76

SQL is a mind bender for me. I do a lot of work on Data, use python and pandas to do a lot of data magic, but the problem with me is my mind is too procedural in thinking. - Step 1 - Step 2 - Loop through results in Step 2 - Curate and finish output. I try very hard to transform the above steps into an SQL statement spanning multiple tables, but always fail and I usually fallback to python for manually extracting and…

I work a lot with data as well primarily I use SAS, R and SQL. I'm also very procedurally oriented (as in I prefer breaking task down into discrete chunks), R's functional way of chaining operations with pipe operator feels so alien to me.

I find myself having the opposite problem I probably use (abuse?) SQL more than should.

My world (industrial plant) is very DB/historian heavy everything speaks SQL it's pretty much the common tongue connecting everything. I think this is slowly changing some PLC/historians now offer a webservice which returns JSON objects via an ajax query - personally I vastly prefer SQL to ajax.

I'd estimate for a typical problem I'm working with maybe 90% is done in SQL vs 10% in R/SAS code.

When I need Regression, Principal Component Analysis, Time Series manipulation, Plots etc I have to break into dedicated language.

For most other things - extract, merge, filtering, high level aggregation (Count sum etc) type of operations using SQL feels more natural and expressive to me.

Re: SQL: One of the most valuable skills

#129

Post by fiddlerwoaroof »

dominotw wrote: post #124
chillacy wrote: post #123

Earlier quoted context omitted.

I understood when OP said the "concept of SQL" that they referred to a pretty broad idea of querying tabular data in rows and columns with some structured language.

I find it hard to accept that ppl are talking about 'querying tabular data in rows and columns with some structured language' when they talk about sql. They are surely talking about the specific syntax. https://en.wikipedia.org/wiki/SQL

And then, when working in a legacy code base, you run into a dark corner inhabited by pre-ANSI joins and get eaten by a gru.
Post reply on HN