Practical SQL for Data Analysis • Hacker News Board

Re: Practical SQL for Data Analysis

#11

Post by ramraj07 » Mon, May 03, 2021, 3:43 PM UTC

nerdponx wrote: post #2

SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases, no easy interoperability with other tools, limited editor/IDE support, etc. I guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL. For everything else, I'll continue using SQL to get the…

Completely disagree. I have a choice of using snowflake and spark/pandas to do my EDA and I’ll choose sql every time. The code is significantly more readable once you get used to it and you can most definitely do things one step at a time using udfs and temp tables / cte s. I’ve come back to EDA I did a year back and it’s always easier to read a long sql script than a notebook with pandas code.

Re: Practical SQL for Data Analysis

#12

Post by simonw » Mon, May 03, 2021, 3:44 PM UTC

jonas_b wrote: post #7

Question, if I have a CSV file that I'd like to do some quick SQL queries on before moving the results into Pandas. What would be good resource to do this? Preferably compatible with the rest of the Python-dataframe ecosystem and as simple as pd.read_csv()

If you want to try it in SQLite (pros: no need to run a server or install anything since it's in the Python standard library, cons: not nearly as many advanced statistical analysis features as PostgreSQL) my sqlite-utils CLI tool may help here: it can import from CSV/TSV/JSON into a SQLite database: https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...

Re: Practical SQL for Data Analysis

#13

Post by simonw » Mon, May 03, 2021, 3:45 PM UTC

sbuttgereit wrote: post #6

The code: WITH dt AS ( SELECT unnest(array[1, 2]) AS n ) SELECT * FROM dt; Is more complex than necessary. This produces the same result: SELECT n FROM unnest(array[1, 2]) n; ┌───┐ │ n │ ├───┤ │ 1 │ │ 2 │ └───┘ (2 rows) I think I see some other opportunities as well. I know the code is from a section dealing with CTEs, but CTEs aren't needed for every situation including things like using VALUES lists. Most people in…

I think that was a deliberate choice by the author to consistently demonstrate CTEs.

Re: Practical SQL for Data Analysis

#14

Post by zabzonk » Mon, May 03, 2021, 3:46 PM UTC

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.

> The days of dedicated SQL programers are mostly gone.

I've never met a "dedicated SQL programmer" - all the C++ programmers I've worked with in the investment banking world were also expected to know, and did know, SQL pretty well - asking questions about it were normally part of the interview process.

Re: Practical SQL for Data Analysis

#15

Post by sbuttgereit » Mon, May 03, 2021, 3:46 PM UTC

simonw wrote: post #13

sbuttgereit wrote: post #6

The code: WITH dt AS ( SELECT unnest(array[1, 2]) AS n ) SELECT * FROM dt; Is more complex than necessary. This produces the same result: SELECT n FROM unnest(array[1, 2]) n; ┌───┐ │ n │ ├───┤ │ 1 │ │ 2 │ └───┘ (2 rows) I think I see some other opportunities as well. I know the code is from a section dealing with CTEs, but CTEs aren't needed for every situation including things like using VALUES lists. Most people in…

I think that was a deliberate choice by the author to consistently demonstrate CTEs.

Sorry was editing to address this point probably while you were typing your response. It's well taken, but we should be clear that it's not required.

Re: Practical SQL for Data Analysis

#16

Post by simonw » Mon, May 03, 2021, 3:47 PM UTC

This article is so useful. It starts out with SQL basics but then quickly leaps into all kinds of PostgreSQL tricks that I didn't know about - binning, efficient sampling, calculating, even linear regression.

Re: Practical SQL for Data Analysis

#17

Post by ithrow » Mon, May 03, 2021, 3:48 PM UTC

jonas_b wrote: post #7

Question, if I have a CSV file that I'd like to do some quick SQL queries on before moving the results into Pandas. What would be good resource to do this? Preferably compatible with the rest of the Python-dataframe ecosystem and as simple as pd.read_csv()

You can easily import the csv file into sqlite and you don't even have to create the table fields beforehand

Re: Practical SQL for Data Analysis

#18

Post by bruiseralmighty » Mon, May 03, 2021, 3:48 PM UTC

jonas_b wrote: post #7

Question, if I have a CSV file that I'd like to do some quick SQL queries on before moving the results into Pandas. What would be good resource to do this? Preferably compatible with the rest of the Python-dataframe ecosystem and as simple as pd.read_csv()

I keep a non-commercial installation of SQLServer on my machine for this reason, but this is likely overkill for most purposes and requires a Windows machine.

It does have some nice import features for CSV data though.

Re: Practical SQL for Data Analysis

#19

Post by rdedev » Mon, May 03, 2021, 3:48 PM UTC

nerdponx wrote: post #2

SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases, no easy interoperability with other tools, limited editor/IDE support, etc. I guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL. For everything else, I'll continue using SQL to get the…

For apache arrow backed dataframes check out Polars(https://github.com/ritchie46/polars)

Re: Practical SQL for Data Analysis

#20

Post by wswope » Mon, May 03, 2021, 3:48 PM UTC

jonas_b wrote: post #7

Question, if I have a CSV file that I'd like to do some quick SQL queries on before moving the results into Pandas. What would be good resource to do this? Preferably compatible with the rest of the Python-dataframe ecosystem and as simple as pd.read_csv()

SQLite is what you're looking for. If you want to use the CLI,

  .format csv
  .import

Alternatively, read your data into pandas and there's extremely easy interop between a DBAPI connection from the python standard lib Sqlite3 module and Pandas (to_sql, read_sql_query, etc.).