How can I compile an SQL statement to generate a Daily Statistics report on the order quantity and growth volume through the order data table?

For example, I have a database with an order table named orders. this order table has a field add_time, that is, the timestamp for adding orders. Now I want to generate a report that displays the daily order quantity and growth, along with the number of days (Y-m-d format... for example, I have a database with an order table named orders. this order table has a field add_time, that is, the timestamp for adding orders.
I want to generate a report, which is displayed on the report. Daily order quantity and growth volumeAnd the number of days (in Y-m-d format, because I want to output the echarts or highcharts statistical chart to the front end). how can I write this SQL statement?
Happy Dragon Boat Festival. thank you for your answers !~

Reply content:

For example, I have a database with an order table named orders. this order table has a field add_time, that is, the timestamp for adding orders.
I want to generate a report, which is displayed on the report.Daily order quantity and growth volumeAnd the number of days (in Y-m-d format, because I want to output the echarts or highcharts statistical chart to the front end). how can I write this SQL statement?
Happy Dragon Boat Festival. thank you for your answers !~

This is because the framework of an operating system has just been built to provide a basic idea:

1. Create a new table and use each item of data you need to calculate and the statistical time as the column of the new table, as shown in the following code:

    create table analysis (        today_timestamp varchar(20),        order_count varchar(20),        increase_value varchar(20),        primary key (today_timestamp)    );

1. Write a php script, suchjob.phpTo get the data you need for the day: the number of orders, the amount of growth, and so on. For example, if you need the quantity of all ordersselect count(*) from ordersThe specific writing is another problem. Insert the table.analysis. If you want it to run automatically, you can use crontab.

2. When youEcharts statistical chart or highcharts statistical chart to be output to the front endYou can read the data in this table by date.

Group by is used for statistics. the date field is divided into years, months, days, hours, and minutes. each column is used for statistics. remember to add indexes and table shards.

You 'd better give a sample data structure for this requirement.

SELECT    FROM_UNIXTIME(add_time, "%Y-%m-%d") order_date,    count(1) AS today_c,    count(1) - last_c AS change_with_lastFROM    ordersLEFT JOIN (    SELECT        FROM_UNIXTIME(add_time, "%Y-%m-%d") AS last_date,        count(1) AS last_c    FROM        orders    GROUP BY        FROM_UNIXTIME(add_time, "%Y-%m-%d")) last ON (    FROM_UNIXTIME(temp.add_time, "%Y-%m-%d") = DATE_ADD(        last.last_date,        INTERVAL 1 DAY    ))GROUP BY    FROM_UNIXTIME(add_time);

This should achieve the expected results, but it is best to use a data table to count the results of the day, otherwise there will be performance problems