I have pandas dataframe like this : created_at lat long hex_ID 0 2020-10-13 15:12:18.682905 28.690628 77.323285 883da1ab0bfffff 1 2020-10-12 22:49:05.886170 28.755408 77.112289 883da18e87fffff 2 2020-10-13 15:24:17.692375 28.690571 77.323335 883da1ab0bfffff 3 2020-10-12 23:21:13.700226 28.589922 77.082738 883…
Tag: postgresql
Filtering UNION ALL result is much slower than filtering each subquery
I have simple select that runs slowly. Exactly the same problem as in https://dba.stackexchange.com/questions/136653/filtering-union-all-result-is-much-slower-than-filtering-each-subquery I wonder is there anything I could do without making a function that accepts krepselis as parameter. There is my explain h…
SQL – ordering table by information from multiple tables
Title of the question may not have been very clear – I am not really sure how to name this question, but I hope that my explanation will make my problem clearer. I have 3 tables: [1] score id rating_type 1 UPVOTE 2 UPVOTE 3 DOWNVOTE 4 UPVOTE 5 DOWNVOTE 6 DOWNVOTE [2] post_score post_id score_id 1 1 1 2
How to optimize min and max to find highest score sum
This problem involves finding two min-max criteria filters to generate the highest score sum in a dataset. I have a dataset, with 3 columns. x, y, score, with over 1 million of rows. x y score 3.6 1.2 -5 4.2 1.2 -4 1.2 30.2 1 2.9 6.8 6 3.1 5.8 7 0.1 15.8 7 The data may or may not
Convert a json which is a list of dictionaries into column/row format in Postgresql
I´ve a json which is a list of dictionaries with the next syntax: The json is really big and each dictionary have different amount of key/values fields. And what I want to do is to create a table in postgresSQL where the key represents a column and the value a row. In the example explained above I would like …
insert same record multiple times based on the given count
I want to insert ABC thrice, EDC four times, FDC twice in a table using single SQL, is it possible? The output of the query should insert into the following table with the following entries. Thanks Answer You would typically use a recursive query: Here is a demo; the syntax works in both Postgres and MySQL 8.…
SQL query to get Most Frequently purchased and Most Recently purchased quantity for a customer-product pair
I have a table Customer_Order_Data with these columns: customer_id order_id product_id quantity order_date For all the dataset available I want to find out the most frequently purchased quantity and most recently purchased quantity for a customer-product pair. Let’s say I have the following data –…
How to update one column of table with 8 million rows of data in PostgreSQL
I have a table with about 20 columns. It currently holds 8 million rows of data. I need to update one NULL column for all 8 million rows, it is a GUID column and a FK for another table column. Currently, I am running simple update query in a loop. But it is taking way too long. Answer Updating 8
How to set default value in sql so that is displays in db
What important in A table is there are three values of type column: New, Activated, Archive. And I set default value 0 to size column of A table and after insert command I expected following result in A table: Query Table A result id b_id type size 1 101 New 10 2 101 Activated 0 3 101 Archive 0 But,
SQL – cartesian product of PARTITIONs
There is a really complicated query, which I cannot wrap my head around alone. In the queried table there are 5 columns: PK_ID, which is the unique identifier of the value, ID, which connects the aggregated values in one group, the count, which says, how many value groups should be aggregated (if there aren&#…