sql - 将一排匹配到两排，无需替换

Question

我想将每个提交订单的人 ( YES) 与两个未提交订单的人() 匹配NO并且具有相同的genderand age。如果一个人与人匹配x，x则不能与其他任何人配对。如果 personx有 a contact_date，它必须大order_date于其原始匹配的 。

ID  Gender  AGE  ORDER  ORDER_DATE    CONTACT_DATE
1    M      18   YES    10/10/2019    Nan
2    F      25   NO     Nan           09/06/2016 
3    M      54   YES    08/04/2018    09/08/2018
4    M      30   YES    09/11/2016    12/ 12/2016

我设法从没有订购的人中选择了两个 ID，并将它们与一个订购的人匹配。但我不知道如何包含条件“如果一个没有订购的人有一个contact_date，它应该大于order_date它的匹配”

CREATE TABLE 1st_match AS (
  SELECT b*.
  FROM (
    SELECT aa.*, ROW NUMBER () OVER(PARTITION BY AGE, GENDER ORDER BY ID1 ASC) rn
    FROM (diag_orderedcostumer aa) a,
    SELECT bb.*, ROW NUMBER()OVER(PARTITION BY AGE, GENDER ORDER BY ID2 ASC) rn
    FROM (diag_notorderedcostumer bb) b
    WHERE a.gender = b.gender 
    AND a.rn = b.rn 
    AND a.AGE = b.AGE
) WITH DATA;

CREATE TABLE 2nd_match AS (
  SELECT b*.
  FROM ( 
    SEL aa.*, ROW NUMBER () OVER(PARTITION BY AGE, GENDER ORDER BY ID1 ASC) rn
    FROM (diag_orderedcostumer aa) a,
    SEL bb.*, ROW NUMBER()OVER(PARTITION BY AGE, GENDER ORDER BY ID2 ASC) rn
    FROM (diag_notorderedcostumer bb
    WHERE ID2 NOT IN (SEL DISTINCT ID2 FROM 1st_match)
  ) b
  WHERE a.gender = b.gender 
  AND a.rn = b.rn 
  AND a.AGE = b.AGE
) WITH DATA;

CREATE TABLE control final AS (
  SELECT * FROM 1st_match UNION ALL
  SELECT * FROM 2nd_match
) WITH DATA;

ID  ID2  ID3   Gender AGE ORDER_DATE  cont_date1   cont_date1  cont_date1
1   30   106   M      18  10/10/2019  Nan          11/12/3019  nan
3   400  1700  M      54  08/04/2018  09/08/2018   nan         09/12/2019
4   256  865   M      30  09/11/2016  12/ 12/2016  1/1/2017    05/12/2018

标签： sqlreplaceconditional-statementsmatchteradata