代码之家  ›  专栏  ›  技术社区  ›  Garis M Suero

如何验证是否有12个连续付款

  •  4
  • Garis M Suero  · 技术社区  · 14 年前

    例如:

    我有这样的场景,我们收到付款,每个家庭的单一付款,并将这些付款的金额登记在 DB .

    问题是一个家庭可以把贷款从 bank1 bank2 只有当他们有12个或更多的连续付款。

    例如,如果他们注册了

    oct, nov, dec, jan, feb, mar, apr, may, jun, jul, ago, and sept.
    

    feb 没有收到任何付款,计数将从开始 march .

    同事们建议最好的方法是,在每个付款登记中,计算总付款,并将总连续付款登记在 int 列称为 sequential .

    AS:

    Payment     Family      Bank     Date          Sequential
    ---------------------------------------------------------
    1200        2           1        10-22-2009    1
    1200        2           1        11-22-2009    2
    .
    .
    .
    1200        2          1        08-22-2010     11
    1200        2          1        09-22-2010     12
    

    我认为,必须有一种方法 相继的 列是不必要的,如果我想验证最后一个 order by Date DESC 12行是连续的,只有 1 差异月份。

    有什么想法吗?

    编辑:

    • 将会有百万的 rows 在这 table .

    • 也希望只在表格中列出日期,并在 application level

    5 回复  |  直到 14 年前
        1
  •  1
  •   user359040    14 年前

    select sum(payment)                          total_paid, 
           count(*)                              total_payments, 
           count(distinct trunc(pay_date,'mon')) paid_months
    from   payment_table
    where  family = :family and pay_date between :start_date and :end_date;
    

    group by bank

    select family,
           sum(payment)                          total_paid, 
           count(*)                              total_payments, 
           count(distinct trunc(pay_date,'mon')) paid_months
    from   payment_table
    where  pay_date between :start_date and :end_date
    group by family
    having count(distinct trunc(pay_date,'mon')) = 12;
    

    and count(distinct bank) = 1 having

        2
  •  5
  •   Adam Musch    14 年前

    create table payments
    (amount       number,
     family       number,
     bank         number,
     payment_date date
    );
    
    insert into payments values (1200, 2, 1, date '2010-01-01');
    insert into payments values (1200, 2, 1, date '2010-02-02');
    insert into payments values (1200, 2, 1, date '2010-03-03');
    insert into payments values (1200, 2, 1, date '2010-04-04');
    insert into payments values (1200, 2, 1, date '2010-05-05');
    insert into payments values (1200, 2, 1, date '2010-06-07');
    insert into payments values (1200, 2, 1, date '2010-07-07');
    --skip august
    --insert into payments values (1200, 2, 1, date '2010-08-08');
    insert into payments values (1200, 2, 1, date '2010-09-09');
    insert into payments values (1200, 2, 1, date '2010-10-10');
    insert into payments values (1200, 2, 1, date '2010-11-11');
    --double pay november
    insert into payments values (1200, 2, 1, date '2010-11-30');
    insert into payments values (1200, 2, 1, date '2010-12-12');
    

    select * 
      from (select family, bank, 
                   trunc(payment_date, 'mon') as payment_month,
                   lead ( trunc(payment_date, 'mon')) 
                     over ( partition by family
                            order by payment_date) 
                     as next_payment_month
              from payments 
             order by payment_date desc 
           )
           -- eliminate multiple payments in month
     where payment_month <> next_payment_month
           -- find a gap
       and add_months(payment_month, 1) <> (next_payment_month)
           -- stop at the first gap
       and rownum = 1
    

        FAMILY       BANK PAYMENT_M NEXT_PAYM
    ---------- ---------- --------- ---------
             2          1 01-JUL-10 01-SEP-10
    

    NEXT_PAYMENT_MONTH


    SELECT trunc(MONTHS_BETWEEN(SYSDATE, DATE '2010-01-01')) FROM DUAL
    

    select trunc(
           months_between(sysdate, 
             (select next_payment_date
                from (select family, bank, 
                             trunc(payment_date, 'mon') as payment_month,
                        lead ( trunc(payment_date, 'mon')) 
                        over ( partition by family
                               order by payment_date) 
                          as next_payment_month
                        from payments 
                       where family = :family
                       order by payment_date desc 
                     )
               where payment_month <> next_payment_month
                 and add_months(payment_month, 1) <> (next_payment_month)
                 and rownum = 1
              ) 
           )
      from dual
    

        3
  •  0
  •   Eduardo Rascon    14 年前

    SELECT COUNT(*)
    FROM payments p
    WHERE p.Family = 2 AND p.Date between '01-01-2009' and '12-01-2009'
    

        4
  •  0
  •   Mike Dinescu    14 年前

    SELECT  PT.Payment
          , PT.Family
          , PT.Bank
          , PT.Date
          , (SELECT COUNT(*) FROM PaymentTable T
             WHERE DATEDIFF (d, T.Date, PT.Date) < 31) as IsSequential
      FROM PaymentsTable PT
    

    SELECT  ST.Family
         , COUNT(ST.IsSequential) as NumberOfSequentialPayments
     FROM
      (SELECT   PT.Payment
              , PT.Family
              , PT.Bank
              , PT.Date
              , (SELECT COUNT(*) FROM PaymentTable T
              WHERE DATEDIFF (d, T.Date, PT.Date) < 31) as IsSequential
         FROM PaymentsTable PT
       ) AS ST
      WHERE NumberOfSequentialPayments >= 12
     GROUP BY ST.Family
    
        5
  •  0
  •   Unreason    14 年前