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

What are the ways to query historical concurrency and total scripts in the database? In order to let you know more about the database, the editor summarizes the following contents. Let's look down together. First, demand 1. Demand background demand 1: statistics of current and historical business volume in the same period and displayed in a field; demand 2: statistics of the total business volume within a specified period of time; 2. 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

All scripts in this article have been uploaded to github:scripts-for-select-now-history-and-sum-from-db

The method of querying the history of the same period and total scripts in the database is shared here, of course, not only the above and everyone's analysis, but the editor can ensure its accuracy is absolutely no problem. I hope that the above content can have a certain reference value for everyone, and can be put into practice. If you like this article, you might as well share it for more people to see.

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.