Shulou(Shulou.com)06/01 Report--

How to query the historical synchronization and total scripts in the database? for this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

First, demand 1. Demand background

Requirement 1: count the current and historical business volume and display it in a field

Demand 2: statistics of the total amount of business within a specified period of time

two。 The final result shows 2.1 the table structure of the simulation table

In this paper, fruit sales are used to simulate the real business volume.

2.2 presentation of current and historical simultaneous effects

Statistics on the sales of fruits in the past 7 days and the same period in history

2.3 display of total effect in a certain period of time

Statistics on the total sales of various fruits in 2019

two。 Script 1. Current and historical simultaneous script #! / usr/bin/ksh# running example: sh fru_calc_day.sh# this script is used to count daily and historical fruit sales # the statistical time starts from D1 days ago, the default is 7, that is, from 7 days ago to the previous D2 days, the default is 1, that is, as of the previous day, the historical data of D2 fruits, the default is 12. That is, in the first 12 months (last year), the log time format filedate= `date + "% Y%m%d%H%M" `# log name REPORT= "/ tmp/fru/report$filedate.log" # database user name / password, fill in the DBUSER=dbuserDBPASSWD=password# database environment variable according to the actual situation. Fill in export ORACLE_SID=mydbexport ORACLE_BASE=/oracle/app/10.2.0export ORACLE_HOME=$ORACLE_BASE/db_1export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATHexport NLS_LANG= "SIMPLIFIED Chinese_CHINA.ZHS16GBK" FRU_CALC () {echo "* * daily fruit sales statistics * according to the actual situation * "> > $REPORTsqlplus-s / nolog > $REPORT 2 > & 1echo" * * end** "> > $REPORT

The execution shell of this script is / usr/bin/ksh, which can be modified according to the actual situation. Run the command echo $SHELL to see the shell type of the current user.

two。 Total script #! / usr/bin/ksh# running example in a certain period of time: sh fru_calc_sum.sh 20190101 2019123 this script is used to count fruit sales volume summary # parameter 1, start time startdate= "$1" # parameter 2 End time enddate= "$2" # Log time format filedate= `date + "% Y%m%d%H%M" `# Log name REPORT= "/ tmp/fru/report$filedate.log" # Database username / password, fill in the DBUSER=dbuserDBPASSWD=password# database environment variable according to the actual situation Fill in export ORACLE_SID=mydbexport ORACLE_BASE=/oracle/app/10.2.0export ORACLE_HOME=$ORACLE_BASE/db_1export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATHexport NLS_LANG= "SIMPLIFIED Chinese_CHINA.ZHS16GBK" FRU_CALC () {echo "* Total sales volume of historical fruits:'$startdate' to'$enddate'*'> > $REPORTsqlplus-s / nolog > $REPORT 2 > & 1echo" * * * end** "> > $REPORT

Statistics 20190101 to 20191231 fruit sales summary.

Third, the running effect is 1. Current and historical queries for the same period

two。 Total query within a certain period of time

This is the answer to the question on how to query the history of the same period and total scripts in the database. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.