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

The report tool generally provides expressions grouped by a certain field, such as grouping statistics by natural month, but sometimes it needs to be grouped by irregular month. For example, an enterprise has implemented a special price strategy or marketing activity since January 16, so it wants to use the 16th of each month as the boundary to calculate product sales in the future. Here, the so-called irregular month refers to: if the starting time is 2013-01-16, then 2013-01-16 to 2013-02-15 as a group, 2013-02-16 to 2013-03-15 as a group. In some cases, statistics need to be made according to numerical intervals, for example, the order amount is divided into four intervals according to 1000, 2000 and 4000, and each interval has a group of orders, and the total amount of each group of orders is counted.

This kind of special grouping is difficult to write in SQL, and reporting tools cannot cope with it. It can only be implemented externally in a hard-coded way, and then integrated in a unique way for each reporting tool. To achieve this kind of requirement, it is generally added to a subset according to the grouping conditions, and aggregation actions such as sum(),count(*),topN(), etc. need to be implemented in the subset respectively. The code is lengthy, and it is troublesome to change and maintain. In fact, the way to use report tool + aggregator for this kind of requirement is very simple. The aggregator code is as follows.

Irregular monthly statistics:

AB1=connect("demo")2=A1.query("select OrderID,Amount,OrderDate from salesall where OrderDate>=？and OrderDate

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.