Please insert 100 random numbers between 74 and 77 to the precision of one decimal point…
- User
Please insert 100 random numbers between 74 and 77 to the precision of one decimal point into a column named value_no in a MySQL table named tbl_progress each with a date for the last 100 days and insert the date into a column named ts. Let the time be 7:00 am for all the dates.
- Assistant
Below is one way to do it using a recursive common table expression (available in MySQL 8.0 and later). This query creates a temporary "days" table with 100 rows (0 through 99), calculates the date for each row as "today minus N days" (with a time of 7:00 AM), and inserts a random value (one decimal place) between 74.0 and 77.0 into the
value_nocolumn.You can run the following SQL:
WITH RECURSIVE days AS ( SELECT 0 AS n UNION ALL SELECT n + 1 FROM days WHERE n < 99 ) INSERT INTO tbl_progress (ts, value_no) SELECT -- Subtract n days from CURDATE() then add 7 hours to set time at 7:00 AM DATE_ADD(DATE_SUB(CURDATE(), INTERVAL n DAY), INTERVAL 7 HOUR) AS ts, -- Generate a random decimal between 74.0 and 77.0, one decimal of precision ROUND(74 + (RAND() * 3), 1) AS value_no FROM days;Explanation
-
Recursive CTE (
days):- The CTE starts with
n = 0(today) and then recursively adds 1 untilnreaches 99. This will produce 100 rows representing the last 100 days.
- The CTE starts with
-
Date Calculation:
- For each row,
CURDATE() - INTERVAL n DAYcomputes the date N days ago. DATE_ADD(..., INTERVAL 7 HOUR)sets the time to 7:00 AM.
- For each row,
-
Random Number:
RAND() * 3generates a random number between 0 and 3.- Adding 74 gives a random number between 74 and 77.
ROUND(..., 1)rounds the result to one decimal place.
Make sure to back up your data and test this in a safe environment before running on production.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.