Skip to main content

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
147 votes
13 answers
99k 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
36k 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 ...
deepguy's user avatar
  • 1,441
20 votes
5 answers
17k 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 ...
AffableAmbler's user avatar
11 votes
3 answers
13k 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 ...
blue-dino's user avatar
  • 383
11 votes
2 answers
7k 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 ...
THE JOATMON's user avatar
10 votes
4 answers
417 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 ...
Little Bobby Tables's user avatar
7 votes
2 answers
427 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
142 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 ...
Steve Kallestad's user avatar
5 votes
1 answer
12k 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 ...
Dipankar Nalui's user avatar
5 votes
1 answer
389 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 ...
Marc's user avatar
  • 222
5 votes
1 answer
51 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 ...
Uri Goren's user avatar
  • 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 ...
Utsav Tiwary's user avatar
4 votes
3 answers
573 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 ...
I Like to Code's user avatar
4 votes
4 answers
98 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 ...
Nicklovn's user avatar
  • 141
4 votes
4 answers
6k 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 ...
Sean McCarthy's user avatar
4 votes
2 answers
146 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 ...
generic_user's user avatar
4 votes
2 answers
8k 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 ...
Munira's user avatar
  • 167
4 votes
1 answer
94 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 ...
bobfet1's user avatar
  • 141
4 votes
2 answers
2k 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 ...
Chandrashekhar Patil's user avatar
4 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 ...
ARAT's user avatar
  • 273
4 votes
1 answer
148 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 ...
blue-dino's user avatar
  • 383
4 votes
0 answers
40 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 ...
Nick Lashinsky's user avatar
3 votes
1 answer
55 views

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

Hello I have done this full join query: ...
helloworld's user avatar
3 votes
1 answer
274 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: ...
user62198's user avatar
  • 1,091
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 ...
Austin Capobianco's user avatar
2 votes
1 answer
3k 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 ...
Dan D.'s user avatar
  • 125
2 votes
1 answer
308 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 ...
aamir23's user avatar
  • 21
2 votes
1 answer
214 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 ...
David Fort Myers's user avatar
2 votes
1 answer
114 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 ...
NacMacFeegle's user avatar
2 votes
1 answer
52 views

Analyze additions and subtractions to/from a data set

I have a data set in the following form: ...
Berbatov's user avatar
  • 121
2 votes
2 answers
50 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 ...
Armin's user avatar
  • 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 ...
Marcin's user avatar
  • 235
2 votes
0 answers
45 views

Designing a data science workflow for a small company

I work at a company with eight employees, and was recently hired as a data/business analyst. The company uses an in-house software to manage online retail sales. There is a huge amount of data ...
Kelsey's user avatar
  • 21
2 votes
1 answer
52 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 ...
ultimatebeginner's user avatar
1 vote
1 answer
4k 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 ...
Muhammad Ali's user avatar
  • 2,487
1 vote
1 answer
387 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-...
Bence Szabó's user avatar
1 vote
1 answer
5k 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 ...
Drj's user avatar
  • 427
1 vote
1 answer
160 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
Mahmoud Adel's user avatar
1 vote
2 answers
84 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 "...
Anatole's user avatar
  • 181
1 vote
1 answer
81 views

Creating a complex featureset for regression modeling

I am currently working a on project that requires me to convert all of the categorical variables to continuous (or binary) variables to build a regression model. The problem is that I have more than ...
Ryan's user avatar
  • 31
1 vote
1 answer
47 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 ...
Sam Cohen-Devries's user avatar
1 vote
1 answer
1k 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 ...
ColRow's user avatar
  • 53
1 vote
1 answer
407 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 ...
Boris Mulder's user avatar
1 vote
1 answer
53 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 ...
harish215s's user avatar
1 vote
1 answer
39 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....
thesimplevoodoo's user avatar
1 vote
1 answer
209 views

SQL and JSON database - R

I have a database (PostgresSQL) which looks like this: ...
MariaKK's user avatar
  • 11
1 vote
1 answer
162 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), ...
Francesco Pegoraro's user avatar
1 vote
1 answer
807 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 ...
Jimmy Ruska's user avatar
1 vote
1 answer
80 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
324 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 ...
Nat's user avatar
  • 148