Как я могу получить дополнение в этом SQL-запросе?

ProgramBox

Как я могу получить дополнение в этом SQL-запросе?

Post author:admin
Запись опубликована:1 февраля, 2023
Post category:Вопросы по программированию

#sql #google-bigquery

Вопрос:

У меня есть база данных bigquery, которая имеет некоторую широту и долготу. Над этой базой данных я запускаю запрос, используя CTE и функцию ST_COVERS bigquery. Общий подмножество составляет 518 строк, а точки, которые применяются к цепочке CTE, равны 423. Как я могу получить оставшиеся строки? Я пытался объединить, объединить все, левое соединение, внутреннее соединение и другие вещи, но я не могу получить всего 518 строк.

PD: я не отправляю запрос, потому что он довольно длинный (вероятно, его можно сильно оптимизировать), но я могу опубликовать его, если это действительно необходимо.

РЕДАКТИРОВАТЬ: это запрос (или наиболее важные его части). С помощью этого запроса я могу получить все lat,lon точки, которые находятся в пределах некоторых полигонов. То, что я ищу, — это также получить lat,lon точки, которые находятся за пределами всех полигонов, и присвоить им тег «NA» в sector столбце.

 WITH staging AS(
  SELECT id,
         lat,
         lon
  FROM
       lat_lon_table),

 area_1 AS(
 SELECT id,
        lat,
        lon,
        "area_1" AS sector,
        ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#some lat lon coordinates))"),
        ST_GEOPOINT(lat,lon)) as `covers`
 FROM
     staging),

 area_2 AS(
 SELECT id,
        lat,
        lon,
        "area_2" AS sector,
        ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"),
        ST_GEOPOINT(lat,lon)) as `covers`
 FROM
     staging),

 area_3 AS(
 SELECT id,
        lat,
        lon,
        "area_3" AS sector,
        ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"),
        ST_GEOPOINT(lat,lon)) as `covers`
 FROM
     staging),

 area_4 AS(
 SELECT id,
        lat,
        lon,
        "area_4" AS sector,
        ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"),
        ST_GEOPOINT(lat,lon)) as `covers`
 FROM
     staging),

 union_all AS(
 SELECT * FROM area_1
 UNION ALL(SELECT * FROM area_2)
 UNION ALL(SELECT * FROM area_3)
 UNION ALL(SELECT * FROM area_4))

 SELECT 
     id,
     lat,
     lon,
     sector
 FROM
     union_all
 WHERE
     covers = TRUE

Ответ №1:

Попробуйте ниже

 #standardSQL
WITH staging AS(
  SELECT id, lat, lon FROM lat_lon_table
), area_1 AS(
  SELECT id, lat, lon, "area_1" AS sector,
    ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#some lat lon coordinates))"), ST_GEOGPOINT(lat,lon)) as `covers`
  FROM staging
), area_2 AS (
  SELECT id, lat, lon, "area_2" AS sector,
    ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"), ST_GEOGPOINT(lat,lon)) as `covers`
  FROM staging
), area_3 AS (
  SELECT id, lat, lon, "area_3" AS sector,
    ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"), ST_GEOGPOINT(lat,lon)) as `covers`
  FROM staging
), area_4 AS (
  SELECT id, lat, lon, "area_4" AS sector,
    ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"), ST_GEOGPOINT(lat,lon)) as `covers`
  FROM staging
), union_all AS (
  SELECT * FROM area_1 UNION ALL
  SELECT * FROM area_2 UNION ALL
  SELECT * FROM area_3 UNION ALL
  SELECT * FROM area_4
)
SELECT id, lat, lon, sector FROM union_all 
WHERE covers = TRUE
UNION ALL
SELECT id, lat, lon, 'NA' FROM lat_lon_table 
WHERE NOT id IN (SELECT DISTINCT id FROM union_all WHERE covers = TRUE)

1. Большое вам спасибо, @Михаил Берлянт! Это сработало :). Есть предложения о том, как я могу улучшить запрос?

2. когда я ответил — я думаю, я что-то имел в виду, но я сосредоточился на точном вопросе и не хотел ничего менять в вашем первоначальном запросе. ОК. Я скоро проверю, и если у вас есть какие-то идеи, опубликую их как отдельный ответ

Ответ №2:

Есть предложения о том, как я могу улучшить запрос?

Попробуйте ниже — он менее подробный, простой в обслуживании и должен быть дешевле и быстрее (не тестировался)

 #standardSQL
WITH staging AS(
  SELECT id, lat, lon FROM lat_lon_table
), areas AS(
  SELECT id, lat, lon, 
    IF(ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#some  lat lon coordinates))"), ST_GEOGPOINT(lat,lon)), 'area_1', 'NA') AS area_1, 
    IF(ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"), ST_GEOGPOINT(lat,lon)), 'area 2', 'NA') AS area_2,
    IF(ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"), ST_GEOGPOINT(lat,lon)), 'area_3', 'NA') AS area_3,
    IF(ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"), ST_GEOGPOINT(lat,lon)), 'area_4', 'NA') AS area_4
  FROM staging
), union_all AS (
  SELECT id, lat, lon, 'area_1' AS sector FROM areas WHERE area_1 = 'area_1' UNION ALL
  SELECT id, lat, lon, 'area_2' FROM areas WHERE area_2 = 'area_2' UNION ALL
  SELECT id, lat, lon, 'area_3' FROM areas WHERE area_3 = 'area_3' UNION ALL
  SELECT id, lat, lon, 'area_4' FROM areas WHERE area_4 = 'area_4' UNION ALL
  SELECT id, lat, lon, 'NA' FROM areas WHERE (area_1, area_2, area_3, area_4) = ('NA', 'NA', 'NA', 'NA') 
)
SELECT * FROM union_all