Telling if entries in table are increasing

formula to detect if row values are increasing
how to find increasing and decreasing intervals on a graph
how to find increasing and decreasing intervals using derivatives
increasing and decreasing functions examples
increasing and decreasing functions - calculus
increasing and decreasing intervals calculator
increasing and decreasing intervals of quadratic functions
how indexing helps in improving performance of query

I've a table temp(person text, year int,count int). It stores

person, year, count
("a",2009,1),
("a",2010,2), 
("a",2011,3), 
("a",2012,4),
("b",2010,1),
("b",2011,2),
("b",2012,3), 
("c",2011,1),
("d",2009,4),
("d",2010,4), 
("d",2011,4), 
("d",2012,4),
("e",2009,1),
("e",2010,2),
("e",2012,4)

I'm supposed to tell that for which person(s) count is strictly increasing from 2009 to 2012 , count is always a positive integer. For the given table, the output will be "a" and "b". Note that "b" also has strictly increasing since it's count was 0 in 2009 (I know it's a little wierd that above it's written that count has to be positive and now I'm saying that it is 0 for 2009, but it's just given like that in the question).

Expected Output:- In the given table, a has strictly increasing from 2009-2012. That's cool. For b, it is missing 2009, but we take that as zero (to be precise, names are the names of authors and count is the number of papers published by them- we have to find those authors who publish more papers in 2010 than 2009, in 2011 than 2010 and 2012 than 2011, so taking count=0 does makes sense). So for b, 2009 is 0. And so, the sequence is 0, 2, 3, 4 - strictly increasing. c shouldn't be printed since count in both 2009 and 10 is zero. Similar, d shouldn't be printed since its count is constant. e shouldn't be printed because after taking 0, its sequence becomes 1, 2, 0, 4. So, a and b should be the only output.

My try:- I tried using lag function, but there's problem in that since it won't be able to distinguish if 2009 is present or not. I can use count in that too, but then I won't be able to differentiate between which one is absent. What would be the most optimal solution for this? Thanks!

Edit:

Based on the new data & more detailed logic this should work (based on Tim's query):

WITH cte AS
 (
   SELECT person, count_, year_
      ,Lag(Count_, 1, 0) Over (PARTITION BY person ORDER BY YEAR_) AS prev_count
      ,Lag(year_, 1,year_-1) Over (PARTITION BY person ORDER BY YEAR_) AS prev_year
   FROM vt
 )
SELECT person
FROM cte
GROUP BY person
HAVING Min(year) <= 2010                  -- only one of the first two years might be missing
   AND Max(CASE WHEN prev_count < count   -- count increased
                 AND prev_year  = YEAR -1 -- no missing year
                THEN 0
                ELSE 1
           END) = 0

Optimization Models in a Transition Economy, Essentially, what the theorem tells us is that if the potential entrant is allowed According to Table 2.1 local business carries out costly activities to deter the entry. the government may improve welfare of the country by blockading the entry of  If you suspect some table as the culprit of the increasing DB size, you can check the exact size of the table along with the entries in the following way: Go to transaction SE38 and execute the program "RSSPACECHECK" or "RSTABLESIZE". It will give you the total size of the table. Hope this helps further your analysis. Best Regards, Shouvik

Use generate_series() left-joined with the table to get data with complete counts:

select year, person, coalesce(count, 0) as count
from generate_series(2009, 2012) as year
cross join (
    select distinct person
    from temp
    ) p
left join temp using(year, person)
order by 2, 1

 year | person | count 
------+--------+-------
 2009 | a      |     1
 2010 | a      |     2
 2011 | a      |     3
 2012 | a      |     4
 2009 | b      |     0
 2010 | b      |     1
 2011 | b      |     2
 2012 | b      |     3
 2009 | c      |     0
 2010 | c      |     0
 2011 | c      |     1
 2012 | c      |     0
 2009 | d      |     4
 2010 | d      |     4
 2011 | d      |     4
 2012 | d      |     4
 2009 | e      |     1
 2010 | e      |     2
 2011 | e      |     0
 2012 | e      |     4
(20 rows)

Use array_agg() on the result to find persons which fulfill the conditions:

select person, array_agg(count order by year) as counts
from (
    select person, year, coalesce(count, 0) as count
    from generate_series(2009, 2012) as year
    cross join (
        select distinct person
        from temp
        ) p
    left join temp using(year, person)
    ) s
group by person
having  array_agg(distinct count order by count) = array_agg(count order by year)

 person |  counts 
--------+-----------
 a      | {1,2,3,4}
 b      | {0,1,2,3}
(2 rows)    

Medical Record, The differences between difierent countries, which the table shows, reproduce I need not enlarge upon the possibilities of improving the diagnosis of cancer in The difiiculties are greater in telling if a person is sufiering from cancer than in  The simplest measure of association for a 2 × 2 contingency table is the odds ratio.Given two events, A and B, the odds ratio is defined as the ratio of the odds of A in the presence of B and the odds of A in the absence of B, or equivalently (due to symmetry), the ratio of the odds of B in the presence of A and the odds of B in the absence of A.

I like klin's solution but you don't need generate_series() and cross join:

select person
from temp t
where year between 2009 and 2012
group by person
having (count(*) = 4 and count(distinct count) = 4 or
        count(*) = 3 and min(year) = 2010 and count(distinct count) = 3
       ) and
       array_agg(count order by count) = array_agg(count order by year) ;

Note that this version also handled strictly increasing meaning that equal counts are not included as valid.

Congressional Record: Proceedings and Debates of the Congress, raising the question of the spillover effects On a health-care system already I recall Our good friend, the majority leader, telling us on November 30 that we If we are not going to have a vote, maybe We ought to have a vote to table the  Table1 created and shown in Name Manager refers to the table which system has given the name while using INSERT/Table (Ctrl+T) feature of excel. Once you create a table, I am sure new rows if they are part of the table will also reflect in filter. In an existing table whenever you create new rows it will be part of table as well as filter.

Formula to detect if row values are increasing? : excel, Any ideas to build a formula that will give a "1" if any of the values in a row are decreasing compared to the In general, you can tell if something is increasing or decreasing using: Why does Vlookup have trouble with unsorted tables? This video explains how to determine if a function given as a table of values is increasing, decreasing, concave up, concave down. This video explains the topics using algebra, not calculus. Video

The National Underwriter: The National Newspaper of Fire and , COMMENT ON A PROPOSED NEW TABLE OF MORTALITY (From the Mutual Life the construction of a new mortality table to be based on the record of_ insured lives, It is doubtful whether there has been any great increase in the longevity of 0f the Agency Managers will tell you what the Company is doing and how it  It’s harder to tell with this view, but the Anchored COUNTIF is still producing the same result, providing a unique, ascending count of appearances for each product in the table. The example above actually demonstrates one of the key features of the Anchored COUNTIF: it still works even if you re-sort your table .

How to use Indexing to Improve Database Queries, Indexing can dramatically increase query speed. If the table was ordered alphabetically, searching for a name could happen a lot faster because we could In the example above the B-tree below limits entries to 4 characters. This output will tell you which method of search from the query plan was chosen and how long  Which table shows a function that is increasing only over the interval (–2, 1), and nowhere else? A 2-column table with 6 rows. The first column is labeled x with entries negative 3, negative 2, negative 1, 0, 1, 2. The second column is labeled f of x with entries negative 6, negative 3, negative 1, 1, 3, 6. A 2-column table with 6 rows.

Comments
  • Is the count always '1 to n* without gaps? And there must be a row for year 2012? And what if there's only a single row for a person with 2012?
  • @dnoeth No. Any year from 2009-2012 could be missing, both inclusive.
  • I wrote that I didn't understand your logic fully. If you can provide more example data and expected result it can easily be fixed (btw, this already solves your b case from Tim's comments)
  • This doesn't scale to arbitrary ranges of years. I believe this is actually a gaps and islands problem baked into the actual OP.
  • @dnoeth The bug in Tim's code was that it was unable to identify the missing years and gave wrong output for cases like c and e
  • @Necessary: Don't post links to screehshots, better modify the question and add more example data
  • @dnoeth Done. Thanks
  • Perfect. But, I kinda wanted to do it using LAG(). But thanks a lot sir. I learnt many new things from your solution :)
  • So short and perfect! Thanks! (But there could only be one accepted answer :( )