SQL statements for report statistics and SQL statement reports

Source: Internet
Author: User

SQL statements for report statistics and SQL statement reports
-- Some Basic SQL functions used
-- Returns the middle-aged value of the date: SQL YEAR function.
-- Return the value of the Month in the date: SQL Month function.
-- Return the value of the date: SQL Day function. -- DATE_ADD (): The function adds the specified time interval to the date.
-- LAST_DAY (): returns the last day of the current month of a time value.
-- MAKEDATE (): returns the year value and the day value in the year, and returns a date.
-- CONCAT: splicing date
-- ELT matching expression
-- SQL EXTRACT () function is used to EXTRACT values (year, month, day, etc.) from a date expression)


-- NOW (): The current time, which can be replaced with any time in DATE format.
-- Get the current time yyyy-mm -- dd hh: mm: ss
Select now () AS times
-- Obtain the current date yyyy-mm-dd
Select date (NOW () AS times

-- Query year, quarter, month


-- Query the last day of a month
SELECT LAST_DAY (NOW () AS times;

-- Query the first day of March January
SELECT DATE_ADD (LAST_DAY (NOW (), INTERVAL 1 DAY) AS times

-- Query the first day of the current month
SELECT DATE_ADD (LAST_DAY (NOW (), INTERVAL 1 DAY), INTERVAL-1 MONTH) AS times


-- Query quarter


-- Query the current quarter
Select quarter (NOW () AS times

-- Query the first month of the quarter
Select elt (QUARTER (NOW (), 1, 4, 7, 10) AS times

-- Obtain the first day of the current quarter
Select date (CONCAT (YEAR (NOW (), '-', ELT (QUARTER (NOW (),), '-1') AS times


-- Obtain the first day of the year
Select makedate (EXTRACT (year from now (), 1) AS times

-- Get the last day of the quarter
SELECT LAST_DAY (MAKEDATE (EXTRACT (year from now (), 1) + interval quarter (NOW () * 3-1 MONTH) AS times


-- Obtain the first day of the first month of the current quarter
SELECT DATE_ADD (LAST_DAY (MAKEDATE (EXTRACT (year from now (), 1) + interval quarter (NOW () * 3-3 MONTH), INTERVAL 1 DAY ), INTERVAL-1 MONTH) AS times

-- Obtain the last day of the first month of the current quarter
SELECT LAST_DAY (MAKEDATE (EXTRACT (year from now (), 1) + interval quarter (NOW () * 3-3 MONTH) AS times
-- Of course, you can change the following multiplication parameter for different quarters.


-- A statistical statement used
-- Achieve the sales volume of a single product in the first month of the corresponding date quarter (sales order statistics) dataEx: A Time Variable
SELECT B. productname productnames, SUM (B. ordercount) ordercounts, SUM (B. subtotal) subtotals FROM orders a, child_orders B WHERE. id = B. orders_id AND B. productname = 'productnames'
AND. create_date> = DATE_ADD (LAST_DAY (MAKEDATE (EXTRACT (year from '"+ dataEx +"'), 1) + interval quarter ('"+ dataEx + "') * 3-3 MONTH), INTERVAL 1 DAY), INTERVAL-1 MONTH) AND. create_date <= LAST_DAY (MAKEDATE (EXTRACT (year from '"+ dataEx +"'), 1) + interval quarter ('"+ dataEx +"') * 3-3 MONTH)
Group by productnames order by a. create_date DESC

Note:
Of course, this is just a query statement, and there are other statements to implement the same function. Here I only use statements that I think can be used. Of course, there is still a lot of work to do to achieve statistics.
Thanks

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.