SQLite CVSTrac

*** 1,33 ****
! (What is?)
! SQL is a very capable language and there are very few questions that it cannot
! answer. I find that I can
! come up with some convoluted SQL query to answer virtually any question you
! could ask from the data.
! However, the performance of some of these queries is not what it should be -
! nor is the query itself
! easy to write in the first place. Some of the things that are hard to do in
! straight SQL are actually very
! commonly requested operations, including:
! 
! Calculate a running total - Show the cumulative salary within a department row
! by row, with each row
! including a summation of the prior rows' salary.
  
! Find percentages within a group - Show the percentage of the total salary paid
! to an individual in a
! certain department. Take their salary and divide it by the sum of the salary in
! the department.
  
! Top-N queries - Find the top N highest-paid people or the top N sales by
  region.
! 
! Compute a moving average - Average the current row's value and the previous N
  rows values together.
! 
! Perform ranking queries - Show the relative rank of an individual's salary
  within their department.
  
! *: Both the "Top-N" and ranking queries could perhaps be implemented by simply having a result row number be returned (after sorting). The row number can then be used to calculate positional-based info. It would be similar to Oracle's "rownum" pseudo-column, but without the limits of Oracle's take.
  
  Analytic functions, are designed to address these issues. They add extensions
  to the SQL language that
--- 1,22 ----
!  What is?
  
! _:SQL is a very capable language and there are very few questions that it cannot
! answer. I find that I can come up with some convoluted SQL query to answer virtually any question you could ask from the data.
! However, the performance of some of these queries is not what it should be -
! nor is the query itself easy to write in the first place. Some of the things that are hard to do in straight SQL are actually very commonly requested operations, including:
  
! *:Calculate a running total - Show the cumulative salary within a department row
! by row, with each row including a summation of the prior rows' salary.
! *:Find percentages within a group - Show the percentage of the total salary paid
! to an individual in a certain department. Take their salary and divide it by the sum of the salary in the department.
! *:Top-N queries - Find the top N highest-paid people or the top N sales by
  region.
! *:Compute a moving average - Average the current row's value and the previous N
  rows values together.
! *:Perform ranking queries - Show the relative rank of an individual's salary
  within their department.
  
! Both the "Top-N" and ranking queries could perhaps be implemented by simply having a result row number be returned (after sorting). The row number can then be used to calculate positional-based info. It would be similar to Oracle's "rownum" pseudo-column, but without the limits of Oracle's take.
  
  Analytic functions, are designed to address these issues. They add extensions
  to the SQL language that

sqlite - Unsupported Sql Analytical Functions