CODEWITHSUNDEEP
sqloracle-database
Tony
Tony

Reputation: 241

Return results only if there are enough records

Thanks in advance for your replies, this is a 2-part question.

The Database would be called: "Production" The Table would be called: "FruitHistory"

Sample Data:

enter image description here

Is there a way to query this data such that it will only return results that have at least 3 day's worth of data (as an example).

Ex: The query returns Tony's, David's and Aaron's records but not Bern's


Part2:

Building on Part1's rule but now only returning 3 days worth of data at all

Ex: The query returns 11/3-11/5 for Tony and Aaron and all of David's data but still not Bern's.

Thanks!

Upvotes: 0

Views: 73

Answers (3)

user5683823
user5683823

Reputation:

In Oracle 12.1 or higher, MATCH_RECOGNIZE can do quick work of such requirements. I create the sample data in a WITH clause (not part of the SQL statement - use your actual table and column names). I changed the column names - column names should not have spaces in them (your first column does), and they shouldn't be Oracle keywords (like DAY or DATE). I didn't order the output in any way; if this is the final report, not used in further processing, you can add an ORDER BY clause at the end.

with
  sample_data(customer, dt, fruit) as (
    select 'Tony' , date '2018-11-01', 'Orange'     from dual union all
    select 'Tony' , date '2018-11-02', 'Apple'      from dual union all
    select 'Tony' , date '2018-11-03', 'Pear'       from dual union all
    select 'Tony' , date '2018-11-04', 'Plum'       from dual union all
    select 'Tony' , date '2018-11-05', 'Grape'      from dual union all
    select 'David', date '2018-11-03', 'Orange'     from dual union all
    select 'David', date '2018-11-04', 'Watermelon' from dual union all
    select 'David', date '2018-11-05', 'Cantelope'  from dual union all
    select 'Bern' , date '2018-11-05', 'Kiwi'       from dual union all
    select 'Aaron', date '2018-11-01', 'Orange'     from dual union all
    select 'Aaron', date '2018-11-02', 'Apple'      from dual union all
    select 'Aaron', date '2018-11-03', 'Watermelon' from dual union all
    select 'Aaron', date '2018-11-04', 'Kiwi'       from dual union all
    select 'Aaron', date '2018-11-05', 'Pear'       from dual
  )
select *
from   sample_data
match_recognize (
    partition by customer
    order by     dt desc
    all rows per match
    pattern      ( ^ a{3} )
    define       a as null is null
)
;

Output:

CUSTOMER  DT          FRUIT
--------  ----------  -------------
Aaron     2018-11-05  Pear
Aaron     2018-11-04  Kiwi
Aaron     2018-11-03  Watermelon
David     2018-11-05  Cantelope
David     2018-11-04  Watermelon
David     2018-11-03  Orange
Tony      2018-11-05  Grape
Tony      2018-11-04  Plum
Tony      2018-11-03  Pear

Upvotes: 1

MatBailie
MatBailie

Reputation: 86715

Assuming the DBMS you're using is relatively modern it will have analytic / windowed functions. In which case the following should do the type of thing you describe.

SELECT
  *
FROM
(
  SELECT
    *,
    COUNT(*)     OVER (PARTITION BY customer_name)                           AS cust_record_count,
    ROW_NUMBER() OVER (PARTITION BY customer_name ORDER BY day_column DESC)  AS cust_record_id
  FROM
    yourTable
)
  summarised
WHERE
      cust_record_count >= 3
  AND cust_record_id    <= 3

Upvotes: 2

ChrisG
ChrisG

Reputation: 1251

First part:

SELECT Name, Count(*)
FROM table
GROUP BY Name
HAVING Count(*)>2

Second Part:

SELECT *
FROM table
WHERE Name IN (SELECT Name
FROM table
GROUP BY Name
HAVING Count(*)>2)

Upvotes: 0

Related Questions