Oracle Analytics function over

Recently in an OA system statistical module, there is a function need to count the daily new users and cumulative new users, only a user login table (user login time, user ID, etc.),:

Analysis: 1, the same user can log on multiple times in a day, in this day table, there will be more than one record of this user, but when the statistics, can only be counted once

2, will certainly use the logon time group, the user ID deduplication, the data statistics

Because it is a previous project, various restrictions, it must be written with a SQL, check for a long time, SQL as follows:

<!--total users-    <select id= "datalistpage" resulttype= "Usertotlevo" parametertype= "page" >        Select Daytime,xinzeng,totle from (        select Daytime,xinzeng,sum (Xinzeng) over (order by  Daytime) as Totle from (        Select Daytime, COUNT (distinct user_uuid) as Xinzeng from (        select  to_char (to_date (create_time, ' yyyy-mm-dd Hh24:mi:ss '), ' yyyy-mm-dd ') as daytime, user_uuid from        m_logging_info) WHERE 1=1        <if test= "pd.laststart!= Null and pd.laststart!= "" > and            Daytime  >= #{pd.laststart}        </if>        <if test= " Pd.lastend!=null and pd.lastend!= "" > and            Daytime  <= #{pd.lastend}        </if>        GROUP BY Daytime ORDER BY daytime DESC) ORDER BY daytime desc    </select>

Two points: 1,oracle analysis function over

2, the conversion of time format, add a weight, just can count the daily user volume

This microblog is only for documenting its own growth experience, without an introduction to analytic functions. Want to study can go: https://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_over.html

Oracle Analytics function over