Questions tagged [sql]

Structured Query Language (SQL) is a language for querying databases. Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used.

Filter by
Sorted by
Tagged with
139 votes
12 answers
92k views

Why do people prefer Pandas to SQL?

I've been using SQL since 1996, so I may be biased. I've used MySQL and SQLite 3 extensively, but have also used Microsoft SQL Server and Oracle. The vast majority of the operations I've seen done ...
26 votes
5 answers
34k views

Natural Language to SQL query

I have been working on developing a system "Converting Natural Language to SQL Query". I have read the answers from the similar questions, but was not able to get the information that I was looking ...
  • 1,431
20 votes
5 answers
16k views

Do modern R and/or Python libraries make SQL obsolete?

I work in an office where SQL Server is the backbone of everything we do, from data processing to cleaning to munging. My colleague specializes in writing complex functions and stored procedures to ...
11 votes
2 answers
6k views

Tools for automatic anomaly detection on a SQL table?

I have a large SQL table that is essentially a log. The data is pretty complex and I'm trying to find some way to identify anomalies without me understanding all the data. I've found lots of tools for ...
10 votes
3 answers
9k views

Which is faster: PostgreSQL vs MongoDB on large JSON datasets?

I have a large dataset with 9m JSON objects at ~300 bytes each. They are posts from a link aggregator: basically links (a URL, title and author id) and comments (text and author ID) + metadata. They ...
  • 373
10 votes
4 answers
404 views

How to debug data analysis?

I've came across the following problem, that I recon is rather typical. I have some large data, say, a few million rows. I run some non-trivial analysis on it, e.g. an SQL query consisting of several ...
7 votes
2 answers
378 views

How important is advanced SQL for data science?

Is advanced level SQL required to be competitive as a data scientist? Is it more important for a data analyst to be good in SQL? Is it enough to be able to extract data using simple SQL queries? I ...
user avatar
6 votes
2 answers
138 views

How to best accomplish high speed comparison of like data?

I attack this problem frequently with inefficiency because it's always pretty low on the priority list and my clients are resistant to change until things break. I would like some input on how to ...
5 votes
1 answer
9k views

Python & Pandas : TypeError: to_sql() got an unexpected keyword argument 'flavor'

I want to store JSON Data into MySQL Database using Python. I used dataframe of pandas. I found that ...
  • 155
5 votes
1 answer
292 views

Data engineering good and bad practice?

I'm a Data Analyst in a pretty big company and I'm having a really bad time with the data I'm being given. I spend about 70% of my time thinking about where to find the data and how to pull it instead ...
  • 222
5 votes
1 answer
47 views

Data enrichment of geographical records

I have a user_data table with various fields, some of them are based on geography. I'd like to enrich the data with additional columns, like ...
  • 438
5 votes
3 answers
1k views

Storing Sensor Data for Analysis of the Office

I have currently been tasked with designing an application that tracks several different measurements around the office, eg. the temperature, light, presence of people, etc. Having never really worked ...
4 votes
3 answers
563 views

The right join for the right situation?

(Please pardon the pun in the question title.) I'm currently teaching an introductory class to data science. I understand the differences between a left join, a right join, an inner join and a full ...
4 votes
4 answers
89 views

How do Data Scientists integrate Predictive Modeling with SQL?

I've heard a lot about the importance of knowing SQL but how do you guys at the larger firms integrate your software for modeling/analysis with the server that holds the data? Do you simply access ...
  • 141
4 votes
4 answers
5k views

Is there a package for using SQL to manipulate Pandas dataframes in Python?

Rather than learn a new package/language, I'd like to use my existing SQL skills to manipulate pandas dataframes in Python. Does anyone know of a way to do this, or ...
4 votes
2 answers
138 views

Seeking advice on database architecture -- given my problem, what tools should I learn?

I'm a fairly experienced R user, but until now I haven't had a good reason to learn to use databases. Now I have a problem where I am dealing with model output that I need to save to disk, and then ...
4 votes
2 answers
7k views

Mean across every several rows in pandas

I have a table of features and labels where each row has a time stamp. Labels are categorical. They go in a batch where one label repeats several times. Batches with the same label do not have a ...
  • 147
4 votes
1 answer
90 views

Data store for testing data products?

Is there a recommended approach for storing processed data for testing new data products? Basically, I'd like to have a system where a data scientist or an analyst could think of a new data product ...
  • 141
4 votes
2 answers
1k views

How to deal with millions or rows of data for analysis/visualization purpose [closed]

I have data in 2 tables in Sql server. First table has around 10 million rows and 8 columns. Second table has 6 million rows and 60 columns. I want to import those tables into a Python notebook ...
4 votes
1 answer
133 views

Traversing trees in SQL: JOINs vs imperative algorithm

I have a table representing posts on a message board. Posts may or may not have parents. What is the most common way to get all posts starting from a given post, or to find the root post of any given ...
  • 373
4 votes
0 answers
39 views

Method to create master product database to validate entries, and enrich data set [closed]

We have a ruby-on-rails platform (w/ postgreSQL db) for people to upload various products to trade. Of course, many of these products listed are the same, while they are described differently by the ...
3 votes
1 answer
51 views

How to find the values that never matched in a full join

Hello I have done this full join query: ...
3 votes
4 answers
5k views

What is the best way to read SQL dataset in to Tensorflow?

What is the best way to read SQL database in to Tensorflow? Currently, I am using Postgres on server and developed DL algorithm on Tensorflow using Jupyter Lab. How can I import data into Jupyter Lab ...
  • 243
3 votes
1 answer
258 views

How do write the following piece of code in R?

I am trying to write a piece of code for R to fetch data from an api with the following command: ...
  • 1,057
2 votes
3 answers
2k views

Instead of one-hot encoding, can I store the same information in one column using a single value?

Rather than creating 15 additional columns full of sparse binary data, could I: 1) use the first 15 prime numbers as indexes for the 15 categories 2) store data by multiplying the prime numbers of ...
2 votes
1 answer
2k views

How to change data source names in Google Data Studio

Google Data Studio has connectors to MySQL, PostgreSQL, etc. but these connections will come with default names. Couldn't find out how to set the names for data sources in Google Data Studio. Is it ...
  • 125
2 votes
1 answer
301 views

NoSQL vs SQL backend for semi structured data

I have a corpus of job descriptions and another corpus of CVs of applicants. I plan to implement a matching system using machine learning algorithms, to find top 5 or top 10 applicants for each job ...
  • 21
2 votes
1 answer
176 views

SQL Server and skip first n rows

I work for a fire and EMS dispatch agency in Florida. All of our apparatus have gps to record the longitude, latitude, timestamp, apparatus, speed, unit status and the incident number. The klaxon in ...
2 votes
1 answer
99 views

Format of data in SQL for machine learning

I am a beginner at Machine Learning and am starting out on a ML project. I have a large chunk of the source material and have started extracting the data from it to be stored in SQL (initial test with ...
2 votes
1 answer
45 views

Analyze additions and subtractions to/from a data set

I have a data set in the following form: ...
  • 121
2 votes
2 answers
44 views

Find closest numbers approximately

I have a table like this; all columns are numeric: TableTest Col1 Col2 Col3 Col4 5 6 7 8 12 6 5 6 2 3.5 6 1 And I want to ...
  • 53
2 votes
1 answer
4k views

How to extract a column that has the highest value within row in Hive?

I have a table, more or less in the following format col1 col2 col3 ... col100 val1 val2 val3 ... val100 Where val* are doubles. Is there a way to extract for ...
2 votes
1 answer
48 views

Storing and collecting data [closed]

I wanted to ask you which coding language I should use. I am pretty new to coding and could really use some help. As I mentioned above I want to create a survey and afterward store my results in a ...
1 vote
1 answer
3k views

How to plot time series data from SQLite database

I am trying to plot time series data by accessing the SQLite database. I successfully connect the database by coding the below script,however, i couldn't figure out how to plot the time series data ...
  • 2,377
1 vote
1 answer
360 views

What should I master better for professional data science in economics and finance? [closed]

First, excuse me for the noob and long question which is probably doesn’t even belong to here, I know there are several question been answered like this out there, but I think this is going to be up-...
1 vote
1 answer
4k views

Left Join with b.key being NULL in R

I am trying to replicate the below sql query in R select a.*, b.key from Table1 a LEFT OUTER JOIN Table2 b on a.key = b.key where b.key is null I have read ...
  • 427
1 vote
1 answer
64 views

Convert This Sql Query into Python pandas Code [closed]

Can help me to solve it with python pandas i don't need any help from python libraries i need code of python Thx so much more for helping me
1 vote
2 answers
48 views

How to generate test set with no data-leakage using multiple columns

I am developing a fraud detection algorithm. Among other things, my dataset contains the phone number, email address and a few other fields that should uniquely identify a user (let's call them "...
  • 171
1 vote
1 answer
36 views

Optimization technique for iterative distribution

Apologies if this does not fit the proper format for this site, as it is a somewhat general question. I have an application that sits on top of a SQL database, and need to handle a process which is ...
1 vote
1 answer
704 views

Get the average time between first and 2nd call (postgresql)

I have the following data in table where I want to calculate the average time between 1st and 2nd call. I know how to get the average, but I have a though time to figure out how to subtract the 2nd ...
  • 53
1 vote
1 answer
304 views

Removing outliers with orders of magnitude differences

I have a dataset of virtual currency earn and spend events from a mobile game app. Unfortunately, people cheat in the game to get more currencies. These cheaters use different techniques so its quite ...
1 vote
1 answer
31 views

How to group by ID, and output without repeating IDs? [closed]

Input UserID Profile 101 apple 102 banana 103 pie 101 banana 102 pine 103 berry Output ...
1 vote
1 answer
28 views

Scraping mixed elements and passing to SQL

I'm running webscrapes via python which are retrieving data from csv's hosted on the web. I'd like to pass the data into a MSSQL database. An issue I have is the mixed elements/data types in the csv....
1 vote
1 answer
195 views

SQL and JSON database - R

I have a database (PostgresSQL) which looks like this: ...
  • 11
1 vote
1 answer
144 views

How to store subset of columns from a csv file?

I need to create a table in hive (or Impala) by reading from a csv file (named file.csv), ...
1 vote
1 answer
685 views

What algorithm could be used to fuzzy merge multiple datasets?

Problem Description I have several tables that are related but do not share any unique key. I've come across this problem several times with customer data in separate source systems that needs to be ...
1 vote
1 answer
76 views

Call a SQL procedure, for each group by [closed]

I am trying to call a procedure for each group of data. For example, I have a list of customers and movie's features that they like. Is a relation one to many, I have many records for one specific ...
user avatar
1 vote
1 answer
321 views

Data dashboard for SQL server database

I am working on developing a data dashboard / app for a Microsoft SQL database. Currently, I am developing the dashboard using Shiny and R. The app is mostly for exploratory analysis to allow people ...
  • 148
1 vote
1 answer
77 views

How to visualize trending over geographical area?

I would like to visualize how a percentage evolves in time over a geographical area. In particular I have the percentage of products delivered in different regions and I want to show the weekly trend ...
1 vote
2 answers
2k views

Does SQL Server support the Poisson distribution?

I am trying to do some work that is primarily based in SQL Server. I cannot seem to find a native function to SQL that supports the Poisson distribution. Has anyone had success with applying Poisson ...