After several days of trial and error,
As I can't solve this problem, I thought it might be rude to ask questions here because I'm a beginner and the content is rudimentary.
I would appreciate it if you could allow me to post a post to help you solve this problem.
This is an example of a fruit.
In MySQL, click
·At the customer company specified by company_id,
·The buy_date for each fruit is
·Data that is not available after today will be extracted
·Extract data from data prior to yesterday and for the maximum date
·If more than one record of the maximum date is applicable, all records are extracted
I'd like to extract records for the .
I was able to successfully extract the record using NOT EXISTS.
Now that the number of records is approximately 90,000, it takes about 20 seconds to extract them.
So I tried and tried, but
On the Internet, I found a page of development advice about replacing NOT EXISTS with LEFT JOIN and accelerating it.
I reorganized the SQL statement, but I got a grammatical error in the SQL statement.
SQL SQL Execution Error #1109.Database Message
"Unknown table 'f1' in where cause"
I believe that the scope of f1 is not valid even in SQL statements, but
Even if I look into the workaround, I can't solve it through trial and error, so I'm in trouble.
How can I use this SQL extraction method to create a SQL statement?
▼food table structure
CREATE TABLE `food`( `id`int(11)NOT NULL auto_increment, `company_id`int(11) NOT NULL default '0', `food_id`int(11) NOT NULL default '0', `buy_date`date default NULL, `food_name`varchar(255) default NULL, PRIMARY KEY(`id`), KEY`company_id`(`company_id`), KEY`food_id`(`food_id`), KEY `buy_date`(`buy_date`), ENGINE=InnoDB DEFAULT CHARSET=utf8;
▼ Food table data example
int int verchar(255) date int... AutoIndent key key... primary key --------------------------------------------------------------- id food_id food_name buy_datecompany_id... --------------------------------------------------------------- 11 Tangerine 2018-03-01 100... 21 Tangerine 2018-03-01 200... ★ To be extracted 32 Apple 2018-03-05 200... 43 Banana 2018-02-23 200... ★ To be extracted 52 Apple 2018-03-10 200... ★ To be extracted 65 None 2017-12-31200... 72 Apple 2018-03-19100... 84 Mango 2018-05-30200... *After today No 952018-03-20200... 本日After today ： ： ： ： ： ： ---------------------------------------------------------------
From the table above,
I would like to extract id=2,4,5.
▼ A method of writing found on the Internet as a way to speed up NOT EXISTS.
01 SELECT* 02 FROM 03 food ASf1 04 LEFT JOIN( 05 SELECT 06 DISTINCT f2.food_id 07 FROM 08 food ASf2 09 WHERE 10 (f2.company_id=200) 11 AND (f1.company_id=200) 12 AND (f1.food_id=f2.food_id) 13 AND (f1.buy_date<f2.buy_date) 14)LOG_TEMP 15 ON 16 LOG_TEMP.food_id = f1.food_id 17 WHERE 18 LOG_TEMP.food_id is null;
▼ SQL statements that are currently in use but are significantly slower due to increased records.
·Total number of records in the table: approximately 90,000
·Number of records extracted: 812
·Processing time: approximately 18-22 seconds
SELECT* from food ASf1 WHERE NOT EXISTS ( SELECT1 from food ASf2 WHERE (f2.company_id=200) AND (f1.company_id=200) AND(f1.food_id=f2.food_id) AND (f1.buy_date<f2.buy_date) )
▼Report of the results of this case (add below March 22, 2018)
Final SQL statement resolved.
·Processing speed: approximately 20 seconds → approximately 0.15 seconds
·Extract records at the time of confirmation: 781 cases (the number of cases changes every minute because it is a server in operation)
·Contents of extracted records: Same contents as before improvement ·Number of cases
·Results: Successful improvement!!
·Last SQL statement
SELECT f2.* from ( SELECT food_id,MAX(buy_date)AS max_buy_date from food WHERE company_id = 200 GROUP BY food_id ASf1 INNER JOIN FOOD ASf2 ON (f1.food_id=f2.food_id) AND (f1.max_buy_date=f2.buy_date) WHERE (f2.company_id=200)
Sorry for the confusion, but
Thank you for pointing out the appropriate SQL statement.
Thank you for your time and consideration.
If you want to extract records for maximum days, we recommend that you use the aggregation function
MAX instead of using
NOT EXISTS or
LEFT OUTER JOIN."I heard that ""data from yesterday or earlier"", but the definition of ""strong>yesterday" was ambiguous, so I compared it to ""code>CURDATE() for now."Watch out for time zones in MySQL operating environments.
SELECT food_id,MAX(buy_date) FROM food ASf1 WHERE company_id = 200 AND buy_date<CURDATE() GROUP BY food_id
This gives you the maximum
SELECT f2.* FROM(SELECT food_id,MAX(buy_date)AS max_buy_date FROM food WHERE company_id = 200 AND buy_date<CURDATE() GROUP BY food_id)ASf1 INNER JOIN FOOD ASf2 ON f1.food_id=f2.food_id AND f1.max_buy_date=f2.buy_date WHERE f2.company_id = 200
You can find the appropriate line.There was no mention of multiple records for the maximum date, so I try to list them all for now.
Data that is not available after today is extracted
This is where
NOT EXISTS and
LEFT OUTER JOIN appear.But
Extract data from yesterday and up to date
In conjunction with , you can simply say, "Exclude if the maximum date is today or later."
SELECT f2.* FROM(SELECT food_id,MAX(buy_date)AS max_buy_date FROM food WHERE company_id = 200 GROUP BY food_id)ASf1 INNER JOIN FOOD ASf2 ON f1.food_id=f2.food_id AND f1.max_buy_date=f2.buy_date WHERE f1.max_buy_date<CURDATE() AND f2.company_id=200
Is that so?
362 To Limit Column Values to Strings in a String List Using sqlalchemy
353 I want to create an array of sequences from "1" to a specified number.
351 I have saved several codes written in python to a visual studio file.
361 Add dataframe values of the same structure without column names
374 I want an event to happen when I click on a particular image on tkinter.
© 2023 OneMinuteCode. All rights reserved.