How to keep the same statistics for Oracle, MySQL, Hive and other databases
It is OK to use Group by to count the same data in a field in the database, but it is a bit troublesome to attach a sequence condition, for example, to count each player's winning streak or losing streak. Oracle has window analysis functions, which need multi-layer nesting plus advanced analysis functions to achieve. Mysql, Hive and other databases are not as powerful as Oracle, so it is more difficult to achieve. As the middle computing layer of application and database, aggregator can solve this kind of problem in a unified way. Let's take a look at a simple example. The table logs is as follows. What are the numbers that appear more than 3 times in a row in num?
+-+ +
| | id | num |
+-+ +
| | 1 | 1 |
| | 2 | 1 |
| | 3 | 1 |
| | 4 | 2 |
| | 5 | 1 |
| | 6 | 2 |
| | 7 | 2 |
+-+ +
The code for the aggregator looks like this
A1 $select id,num from logs2=A1.group@o (num) 3=A2.select (~ .len () > = 3). (~ .num)
A1:sql fetch
A2: grouped according to the same value of adjacent num
A3: select the same num at least three times in a row
If you want to know the maximum number of records with the same continuous value, the expression in A3 can be changed to = A2.max (~ .len ()).
Some requirements do not have the same value in order, but have the same trend in order. For example, to find out the records of those months with consecutive losses of three months or more, it is very simple to use the aggregator. Just change the adjacent data grouping rules from the equivalent condition to the trend condition. The code reference is as follows:
A1=db.query ("select * from income and expenditure statement order by month") 2=A1.group@o (income > expenditure). Select (~. Income = 3) .conj ()
The merging of attendance records and entry and exit status is a similar situation. E-commerce, logistics and banks often encounter such requirements as counting the total amount of continuous transactions, continuous landing days, continuous landing start and end time, interval days, and so on. These examples can be quickly implemented. In fact, there are many situations where it is very inconvenient to use SQL, but it is very simple to use the aggregator. If you are interested, you can take a look at the aggregator optimization SQL sample summary. The aggregator provides a Jdbc interface, and Java can be called directly, which is very convenient to use. For information about the installation and use of the aggregator, obtaining free authorization and related technical materials, see how to use the aggregator.
