SQL query to identify 0 AFTER a 1

Question

Let's say I have two columns: Date and Indicator

Usually the indicator goes from 0 to 1 (when the data is sorted by date) and I want to be able to identify if it goes from 1 to 0 instead. Is there an easy way to do this with SQL?

I am already aggregating other fields in the same table. If I can add this to as another aggregation (e.g. without using a separate "where" statement or passing over the data a second time) it would be pretty awesome.

This is the phenomena I want to catch:

Date      Indicator
1/5/01    0
1/4/01    0
1/3/01    1
1/2/01    1
1/1/01    0

rejoin on the table 1 record ahead or behind and say `where A != B` — DLeh, Mar 09 '15 at 19:44
What do you mean by "goes from"? Are you talking about row updates or about differences between similar rows? — John Bollinger, Mar 09 '15 at 19:46
Can you claify what you are asking? this could be a number of things — theDarse, Mar 09 '15 at 19:49
When I said "goes from" I said "when the data is sorted by date" as in day x it is 1 and day x+1 it is 0. — Chris, Mar 09 '15 at 19:56

user2864740 · Accepted Answer · 2015-03-09T21:00:22.363

2

This isn't a teradata-specific answer, but this can be done in normal SQL.

Assuming that the sequence is already 'complete' and x_n+1 can be derived from x_n, such as when the dates are sequential and all present:

SELECT date -- the 1 on the day following the 0
FROM r curr
JOIN r prev
-- join each day with the previous day
ON curr.date = dateadd(d, 1, prev.date)
WHERE curr.indicator = 1
  AND prev.indicator = 0

YMMV on the ability of such a query to use indexes efficiently.

If the sequence is not complete the same can be applied after making a delegate sequence which is well ordered and similarly 'complete'.
This can also be done using correlated subqueries, each selecting the indicator of the 'previous max', but.. uhg.

edited Mar 09 '15 at 21:00

answered Mar 09 '15 at 20:06

user2864740

60,010
15
145
220

I think this one is more flexible. This one works for indicators or any variable that I want to check for directional changes. – Chris Mar 09 '15 at 20:12
*`correlated`* sub-queries. – MatBailie Mar 09 '15 at 20:35
@MatBailie Thanks for the terminology correction.. old patterns die hard. – user2864740 Mar 09 '15 at 21:00

MatBailie · Answer 2 · 2015-03-09T20:36:14.943

Joining the table against it self it quite generic, but most SQL Dialects now support Analytical Functions. Ideally you could use LAG() but TeraData seems to try to support the absolute minimum of these, and so so they point you to use SUM() combined with rows preceding.

In any regard, this method avoids a potentially costly join and effectively deals with gaps in the data, whilst making maximum use of indexes.

SELECT
  *
FROM
  yourTable   t
QUALIFY
  t.indicator
  <
  SUM(t.indicator) OVER (PARTITION BY t.somecolumn /* optional */
                             ORDER BY t.Date
                         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
                        )

QUALIFY is a bit TeraData specific, but slightly tidier than the alternative...

SELECT
  *
FROM
(
  SELECT
    *,
    SUM(t.indicator) OVER (PARTITION BY t.somecolumn /* optional */
                               ORDER BY t.Date
                           ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
                          )
                            AS previous_indicator
  FROM
    yourTable   t
)
  lagged
WHERE
  lagged.indicator < lagged.previous_indicator

This should be the answer - it addresses the specific implementation. — user2864740, Mar 09 '15 at 21:02

score 1 · Answer 3 · answered Mar 09 '15 at 20:00

1

Supposing you mean that you want to determine whether any row having 1 as its indicator value has an earlier Date than a row in its group having 0 as its indicator value, you can identify groups with that characteristic by including the appropriate extreme dates in your aggregate results:

SELECT
  ...
  MAX(CASE indicator WHEN 0 THEN Date END) AS last_ind_0,
  MIN(CASE indicator WHEN 1 THEN Date END) AS first_ind_1,
  ...

You then test whether first_ind_1 is less than last_ind_0, either in code or as another selection item.

answered Mar 09 '15 at 20:00

John Bollinger

160,171
8
81
157

This meets my creteria so it's a correct answer and I am selecting it as such. What if, instead of an indicator, I am looking at a value to decrease from one date to the next. My actual data isn't an indicator but IS an integer that I want to check for to see if it decreases so I chose a bad example here. – Chris Mar 09 '15 at 20:10

SQL query to identify 0 AFTER a 1

3 Answers3