主页 / dba / 问题 / 316567
Accepted
Phil Tomlinson
Asked: 2022-09-08 07:51:30 +0800 CST

如何选择一个字段中的最低日期高于另一字段中的日期

  • 772

以下目标实际上并不是我的查询的目的，但我用它作为类比来更简单地解释我想要实现的目标：

我正在尝试构建一个 BigQuery 脚本，它查看所有触发了破坏警报的街机机器，然后识别所有那些自警报以来没有进行自动售货机的机器，或者第一次售货机超过 28 台的机器在触发警报后的几天内，以识别在触发警报后可能发生免费使用的机器。

到目前为止，我已经突出显示了所有这些实例，但是在我将 Payments 表加入查询的情况下，所有在每台机器的警报返回后 28 天以上进行的交易，我只对在警报。

我有 3 张桌子

警报

machine_num 报警日期
111 2022-01-20
222 2022-01-20
123 2022-01-20
456 2022-01-20

顾客

客户编号 machine_num
1 111
2 222
3 123
4 456

付款

客户编号 销售日期
1 2022-01-10
1 2022-01-21
1 2022-02-21
2 2022-01-11
2 2022-01-19
3 2022-01-01
3 2022-01-10
3 2022-03-01
3 2022-03-03
3 2022-03-04
4 2022-01-19
4 2022-04-20
4 2022-04-21

所以在这种情况下： cust_num "1" 不会被返回，因为在警报后不到 28 天有售卖 cust_num "2" 将返回售卖日期为 NULL，因为自警报 cust_num 以来没有进行任何售卖“3” 将返回销售日期为“2022-03-01”，因为这是警报 cust_num 之后的第一个 Vend “4” 将返回销售日期为“2022-04-20”，因为这是报警后的第一个 Vend

我需要返回所有 3 个字段，因此根据上面的示例，我的输出将是

客户编号 machine_num 报警日期 销售日期
2 222 2022-01-20 无效的
3 333 2022-01-20 2022-03-01
4 444 2022-01-20 2022-04-20

我尝试在我的 select 语句中添加一个子查询，如下所示：

MIN(vend_date)
FROM payments AS paymin
WHERE paymin.vend_date > alarm_date)
AS vend_date

然而，当我将子查询添加到现有查询时，这往往会导致 bigquery 运行的时间比我有耐心等待它的时间更长。

我以前从未在其中一个网站上寻求过帮助，所以如果我在错误的地方或以错误的方式提问，请道歉！我对 BQ 还比较陌生，并且与业内的任何分析师都相距甚远。

非常感谢任何帮助！

干杯

__ 编辑：

所以我放弃了子查询，它太密集了，因为它要查询近 200 万行！

我尝试使用简单的 MIN 并将所有内容组合在一起，类似于这个简化的示例

SELECT
 payments.cust_num,
 alarm.machine_num, p.PAN, alarm.alarm_date, MIN(payments.vend_date) as vend_Date
 
 FROM alarm
    
LEFT JOIN customer
    ON alarm.machine_num = customer.machine_num
 INNER JOIN payments
    ON customer.cust_num =payments.cust_num 

WHERE 
vend_Date > DATE_ADD(alarm.alarm_date, INTERVAL +28 DAY) OR 
vend_Date IS NULL

GROUP BY payments.cust_num, alarm.machine_num, alarm.alarm_date
ORDER BY  payments.cust_num

但是，这并没有选择在 alarm_date 之后的第一个 vend_date 为 NULL 的实例它也只返回比警报日期晚 28 天的第一个日期，而不是当第一个 vend_date 比 alarm_date 之后的 28 天新时的帐户

join subquery

2 个回答

  1. 下面的查询适用于我的系统，并产生您认为我希望它适用于您的输出

     IF OBJECT_ID ('pay' , N'IF') IS NOT NULL
    -- deletes function
        DROP FUNCTION pay
    go
    create function pay()
    returns table
    as return 
            with cte as ----------------------------------------to merge alarm table and customer table
                (select
                customer_num,machin_num,
                (select max(alarm_date) from alarm as a 
                    where a.machin_num= c.machin_num ) as date
                from customer as c)
        select ------------------------------------------------ to marge payment table
            cte.customer_num, cte.machin_num,cte.date,
            case when datediff(day,cte.date,p.vend_date) > 0 then p.vend_date
            else Null end as vend_date,
            datediff(day,cte.date,p.vend_date) as diff
        from  cte  
        left join  peyments as p
            on cte.customer_num= p.cusromer_num
    ------------------------------------------using table valued function to make it easy to join result with itself
    Go
    select distinct p1.customer_num,p1.machin_num,p1.date, min(p1.vend_date)
    from pay() as p1
     where p1.customer_num not in (select p2.customer_num from pay() as p2 where p2.diff between 0 and 28)
     group by p1.customer_num,p1.machin_num,p1.date
     order by p1.customer_num
    • 1
  2. Best Answer

    非常感谢大家的帮助虽然我最终没有完全掌握用户定义的功能，但我确实以类似的方式最终实现了我的目标。

    这是我的最终查询（为了便于阅读而进行了重大更改）并且还有其他条件（我必须制作一个临时付款表，因为客户编号作为字符串存储在该表中，最后有一个额外的数字，以及任何小于 10 的数字字符的开头附加了 0！日期也是字符串，而且格式很疯狂！）

        WITH pay AS( 
  WITH payments AS (
      SELECT 
        CASE
          WHEN LENGTH(customer_num)>9
           THEN LEFT(CAST (CAST(customer_num AS INTEGER) AS STRING),LENGTH(CAST (CAST(customer_num AS INTEGER) AS STRING))-1)
          ELSE CAST (CAST(customer_num AS INTEGER) AS STRING) 
        END AS customer_num,
        PAN,
        CASE
          WHEN LENGTH(vend_Date)=10
           THEN PARSE_DATE('%Y-%m-%d',vend_Date) 
          ELSE NULL
          END AS vend_Date
      FROM `Vend_Records`),
    
    smart AS (
      SELECT
        customer_num, 
        Primary_Machine_Num AS Machine_Num,
        Account_Start_Date,
        Account_End_Date,   
        closure_type,
          (SELECT 
            MAX(PARSE_DATE('%Y-%m-%d',r.CREATION_DATE_TIME)), 
          FROM `alarm` AS r  
            WHERE r.Machine_Num=Primary_Machine_Num) AS alarm_date
      FROM `customer`)
        
  SELECT 
    cte.customer_num, 
    cte.Machine_Num,
    cte.msn AS Meter_Serial_No, 
    cte.alarm_date, 
    CASE WHEN
           DATE_DIFF(p.vend_date, cte.alarm_date, DAY) >0 THEN p.vend_date  
          ELSE NULL
          END AS vend_date,
      CASE
    WHEN DATE_DIFF(vend_date, alarm_date,DAY) >28
    THEN "vend outside of 28 days"
    WHEN DATE_DIFF(vend_date, alarm_date,DAY)<0
    THEN "No vend after alert"
    WHEN DATE_DIFF(p.vend_date, cte.alarm_date, DAY) BETWEEN 0 AND 28 
    THEN "vend made after alert"
    ELSE "No vend on report"
    END AS Status,
    cte.Account_Start_Date,
    cte.account_End_Date,
    cte.closure_type
  FROM smart
  LEFT JOIN payments AS p
  on cte.customer_num=p.customer_num
  WHERE (alarm_date IS NOT NULL)
  AND DATE_DIFF(Account_Start_Date, alarm_date,DAY)<0 OR Account_Start_date IS NULL
  AND closure_type NOT IN ("COS")

)

SELECT  
  customer_num,
  Machine_Num,
  alarm_date, 
  MIN(vend_Date) AS vend_date,
  Status,
  closure_type
FROM pay AS p1
  WHERE alarm_date > CAST("2021-01-01" AS DATE)
  AND p1.customer_num NOT IN 
                        (SELECT p2.customer_num FROM pay AS p2
                        WHERE p2.status = "vend made after alert")
  GROUP BY customer_num, Machine_Num, alarm_date, status, Meter_Serial_No,PAN, closure_type;
    • 0

相关问题