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

Practical SQL for Data Analysis

hakibenita.com

Post reply on HN
Print view View original

111–120 of 198 posts

Re: Practical SQL for Data Analysis

#111

Post by disgruntledphd2 »

saltcured wrote: post #96
disgruntledphd2 wrote: post #64

Earlier quoted context omitted.

There's pluses and minuses to both. That being said, how do I write a function in SQL which can abstract over something I do a lot (like the pivoting example earlier), or even some date function like iff(date>'important_date', 'before', 'after') as grouper. Honestly, that's what ends up making me move away from doing analytics in SQL.

Edit: oops, I think I replied a level deeper than intended. I was responding to the composition/abstraction topic. I think I should just leave this here now though? I assume you are talking about composition of generic set-processing routines, but I wonder if others realize that? It is easy enough to write a set-returning function and wrap it in arbitrary SQL queries to consume its output. But, it is not easy to writ…

Yeah, that's exactly the issue. One can do this in Python, but not in SQL, and this leads to boilerplate.

I actually think that SparkSQL is a good solution here, as you can create easily reusable functions.

Re: Practical SQL for Data Analysis

#112

Post by jplr8922 »

qwertox wrote: post #93
jplr8922 wrote: post #67

tldr ; if you hear ''but we can do this in SQL'', RUN!!! My eyes hurt as I read this article. There are reasons why analysts dont use SQL to do their job, and it has nothing to do with saving RAM and memory. 1) Data analysis is not a linear process, it involve playing and manipulating the data in different way and letting your mind drift a bit. You want your project in an IDE made for that purpose, the ability to cre…

What is your problem with this example, doesn't it get the job done? WITH temperatures AS ( /* ... */ ) SELECT *, MAX(c) OVER ( ORDER BY t ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS hottest_temperature_last_three_days FROM temperatures; t │ c │ hottest_temperature_last_three_days ────────────┼────┼───────────────────────────────────── 2021-01-01 │ 10 │ 10 2021-01-02 │ 12 │ 12 2021-01-03 │ 13 │ 13 2021-01-04 │ 14 │…

I re-read my comment, and I think that I expressed myself too harshly. If you like SQL and it get the job done for you, no problem. I would see myself using that SQL query.

However in order to get there, you need to know why you need the ''hottest_temparature_last_three_days''. Why not 2 or 4 days? Why not using heating degree day? What about serial correlation with other regions, or metering disfunction? What if you are working directly with raw data from instruments, and still need to choose wich cleaning method you will use? What if you want to check the correlation with another dataset which is not yet in your database (ex: private dataset in excel from a potential vendor)?

If you know exactly what you want, sure SQL is the way to go. However the first step of a data project is to admit that you dont know what you want. You will perform a litterature review and might have a general idea, but starting with a precise solution in mind is a receipe for failure. This is why you could need to fetch all the data and then form results with another tool. How do you even know which factors and features must be extracted before doing some exploration?

If you know the process you need and only care about RAM-CPU optimization, sure SQL is the way to go. Your project is an ETL and your have the job of a programmer who is coding a report. There is no ''data analysis'' there...

Re: Practical SQL for Data Analysis

#113

Post by steve-chavez »

protomyth wrote: post #86
solaxun wrote: post #84

Earlier quoted context omitted.

I understand your point generally but I don't understand this example. If you need data from 15 tables, you need to do those joins, regardless of prefiltering or subgrouping, right?

Well, yes but. Why do you need 15 tables for analysis queries and why isn't someone rolling those tables into something a bit easier with some backend process.

> why isn't someone rolling those tables into something a bit easier with some backend process.

To put it in more concrete terms(plain SQL): the tables could be aggregated on a set returning function or a view.

Re: Practical SQL for Data Analysis

#114

Post by waltherg »

qsort wrote: post #42
tomnipotent wrote: post #37

Earlier quoted context omitted.

> it's that people way overuse it for SQL tasks as this article points out I'm very confused by this. I've used pandas for ever a decade, and in most cases it's a massive time saver. I can do a single query to bring data into local memory in a Jupyter notebook, and from there re-use that memory across hundreds or more executions of pandas functions to further refine an analysis or whatever task I'm up to. Your "copy-…

Pandas is great when you're working on the data manually because it lets you interleave python code and "queries", but it's strictly worse than a plain SQL statement if you're writing, say, a REST service that needs to output some data. SQL executed by the database is orders of magnitude more efficient, way more expressive, and doesn't require you to memorize pandas' absurd API. And I say this as someone who is by no…

This is a great point and way of looking at it: pandas and SQL live at opposite ends of the maturation level of data wrangling pipelines.

Re: Practical SQL for Data Analysis

#115

Post by marktl »

carabiner wrote: post #69
beforeolives wrote: post #3

Nice article. Pandas gets the job done but it's such a step backwards in terms of useability, API consistency and code feel. You can do anything that you can possibly need with it but you regularly have to look up things that you've looked up before because the different parts of the library are patched up together and don't work consistenly in an intuitive way. And then you end up with long lines of().chained().['ex…

Can you honestly say you'd prefer to be debug thousands of lines of SQL versus the usual I learned Pandas first. I have no issue with indexing, different ways of referencing cells, modifying individual rows and columns, numerous ways of slicing and dicing. It gets a little sprawling but there's a method to the madness. I can come back to it months later and easily debug. With SQL, it's just madness and 10x more verbo…

Distinct Count and Group By in SQL will provide distinct Count and Histogram (numerical value) output. Like No SQL vs Relationship, seems there are use cases that lemme them selves to new vs old technologies. It's hard for me to put much stock in opinions from those that have vastly more experience in one tool and not the other being compared.

Re: Practical SQL for Data Analysis

#116

Post by jnsie »

protomyth wrote: post #85
WhompingWindows wrote: post #53

Earlier quoted context omitted.

Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If you need 6 million patients, joining data from 15 tables, gathering 250 variables, a beginning SQL user has the potential to take 15-20 hours where they could be pulling for 1-2 if they do some up-front filtering and sub-grouping within SQL. If you already know…

I don't disagree with writing solid SQL. I would go so far as to say some things (most) need to be in stored procedures that are reviewed by competent people. But, some folks don't think about usage sometimes. This is one of those things I just don't get about folks setting up their databases. If you have a rather large dataset that keeps building via daily transactions, then its time to recognize you really have som…

At its simplest its OLTP vs OLAP. Separate the data entry/transactional side of things from the reporting part. Make it efficient for data analysts do do their jobs.

Re: Practical SQL for Data Analysis

#117

Post by fifilura »

WhompingWindows wrote: post #53
michelpp wrote: post #26

This is an excellent example of what I call the Copy-Object-Copy effect. It's particularly apparent in frameworks with ORMs like Django. In Pandas case, devs will do 'SELECT *' and use pandas as a sort of pseudo ORM. You run a query to get your data as a bunch of objects, but you're copying the data over the db connection from the postgres wire protocol into Python objects in memory, which are typically then garbage…

Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If you need 6 million patients, joining data from 15 tables, gathering 250 variables, a beginning SQL user has the potential to take 15-20 hours where they could be pulling for 1-2 if they do some up-front filtering and sub-grouping within SQL. If you already know…

I can't help thinking that with better processes and tools those 1400 analysts could instead be 50 analysts? (or even 5?)

And that building an aggregation ETL pipeline, maybe inspired by this post, could be the solution?

Re: Practical SQL for Data Analysis

#118

Post by andrewmcwatters »

spamalot159 wrote: post #9

I think for a lot of people, SQL is a skill that doesn't stick. You learn enough to do the queries you need for your project, they work then you forget about them as you work on the rest of your project. These skills are perishable. Left outer join? Yeah, I knew what that was some time ago, but not anymore The days of dedicated SQL programers are mostly gone.

Exactly. I own a software consultancy, and I tried explaining this to an engineering manager and he just didn't get it.

For some reason, he couldn't understand that when you write SQL queries for a project, you typically do it once, and basically never again, with the exception of maybe adding or removing columns from the query.

The "hard work," if you can call it that, is all in the joins. Then, I completely forget about it.

You spend so much more time in development on everything else.

Re: Practical SQL for Data Analysis

#119

Post by thinkharderdev »

codeulike wrote: post #4

I'm so glad to have been around long enough that SQL is now being seen as exotic again

Not long after I got into software engineering is when the first NoSQL craze began. I imagine that those who started not long after me and spent a few years wrangling with MongoDB queries (shudder) and trying to do joins, transactions and consistency guarantees in application code must see RDBMS as some sort of magical new technology.

Re: Practical SQL for Data Analysis

#120

Post by allknowingfrog »

michelpp wrote: post #26

This is an excellent example of what I call the Copy-Object-Copy effect. It's particularly apparent in frameworks with ORMs like Django. In Pandas case, devs will do 'SELECT *' and use pandas as a sort of pseudo ORM. You run a query to get your data as a bunch of objects, but you're copying the data over the db connection from the postgres wire protocol into Python objects in memory, which are typically then garbage…

Is it possible to do any kind of version control with PostgREST? I have no doubt that raw SQL offers huge performance advantages over my ORM, but I'm pretty fond of the ability to roll back to a previous state when I push a bad change to production. Performance is just one of a number of tradeoffs that I consider when I'm choosing tools.
Post reply on HN