BigQuery-ийн хоолойн синтакс: SQL-г илүү хялбар, хурдан, уншихад хялбар болгох

Хамт ажиллаж байсан хүн бүр SQL түүнтэй хамт ирдэг хайр үзэн ядалтын харилцааг мэддэг. SQL нь хүчирхэг, стандартчилагдсан, илэрхийлэлтэй боловч энэ нь бас унших, хадгалахад хамгийн ойлгомжгүй хэлнүүдийн нэг юм. Таны асуулга гүнзгийрэх тусам юу болж байгааг, ямар дарааллаар байгааг ойлгоход хэцүү болно.

Учир нь SQL таны бодсон дарааллаар урсдаггүй. Та бичихээс эхэлнэ SELECT, гэхдээ мэдээллийн сан нь эхлээд үүнийг боловсруулдаггүй. Хөдөлгүүр нь дараахаас эхэлдэг FROM заалт, дараа нь өгөгдлийг нэгтгэж, шүүж, нэгтгэж, дараа нь эрэмбэлэх, проекцийг ашиглана. Үр дүн нь бичсэн асуулга юм буцах түүнийг гүйцэтгэх аргатай харьцуулахад.

Синтакс болон гүйцэтгэлийн хоорондох энэхүү үл нийцэл нь SQL-ийг танин мэдэхүйн хувьд хүнд, тооцооллын хувьд үнэтэй болгодог. Шинжээчид ихэвчлэн дэд асуулга эсвэл нийтлэг хүснэгтийн илэрхийлэлд тулгуурладаг (CTE) шаардлагагүй нарийн төвөгтэй байдал, илүүдлийг нэмж, дээрээс доош урсгалыг дуурайлган хийх. Туршлагатай хэрэглэгчид ч гэсэн шийдэж буй асуудалдаа анхаарлаа төвлөрүүлэхийн оронд логикийн үүрлэсэн блокуудыг дибаг хийдэг.

Үүнийг шийдвэрлэхийн тулд BigQuery supports1s нь уншсан дарааллаар нь ажиллана. Логикийг дотор талд нь бичихийн оронд та үүнийг өгөгдлийн дамжуулах хоолой шиг алхам алхмаар бичдэг - мөр бүр нь тодорхой өөрчлөлтийг илэрхийлдэг. Үүний үр дүнд ойлгоход хялбар, дибаг хийхэд хялбар, орчин үеийн өгөгдөлд дүн шинжилгээ хийхэд илүү ойлгомжтой SQL юм.

Дээрх зураг нь хоолойн синтаксийн үндсэн асуудлыг шийдэж байгааг харуулж байна. Уламжлалт SQL-д синтаксийн дараалал (хэрхэн асуулга бичих) -тай буруу тохируулсан байна семантик дараалал (өгөгдлийн сан үүнийг хэрхэн боловсруулдаг). Жишээлбэл, SELECT дээд талд харагдах боловч хөдөлгүүр нь нэгтгэж, шүүж, бүлэглэж, ангилах хүртэл үүнийг үнэлдэггүй.

Энэ дараалал нь BigQuery хоолойн асуулгын логик болон синтактик урсгалыг - дээрээс доош, зүүнээс баруун тийш - мэдээллийн сан нь өгөгдлийг хэрхэн боловсруулдаг болохыг харуулж байна.

• FROM: Аливаа хоолойн асуулгын эхлэлийн цэг. Өгөгдөл урсах анхны хүснэгт эсвэл дэд асуулгыг тодорхойлно. Хоолойн синтакс дээр, FROM дангаараа хүчинтэй асуулга болж, дараалсан дамжуулах хоолойд орох үүрэг гүйцэтгэдэг.
• НЭГДЭХ: Одоогийн хүснэгтийг өөр хүснэгт эсвэл дэд асуулгын нэмэлт багана, мөрүүдээр өргөтгөнө. Дараалсан хэд хэдэн удаа хэрэглэж болно (|> JOIN table USING (key)), зүүнээс гүнтэй нэгдэх модыг дотоод асуулгагүйгээр унших боломжтой болгох.
• Тохируулах: Байгаа баганын утгыг шинэчилнэ (|> SET column = expression). гэх мэт функцууд SELECT * REPLACE(...) стандарт SQL-д байдаг боловч дамжуулах шугамын алхам болгон ашиглахад илүү уншигдах, модульчлагдсан байдаг.
• СУРГАЛТ: Одоо байгаа өгөгдлийн багцад тооцоолсон багануудыг нэмнэ (|> EXTEND expression AS new_column). SELECT *-тай адил стандарт SQL-ийн new_column боловч нэгдэх болон шүүлтүүр зэрэг бусад үйлдлүүдийн хооронд үүсмэл талбаруудыг аажмаар үүсгэх боломжийг олгодог.
• УНАХ: Одоогийн өгөгдлийн багцаас нэг буюу хэд хэдэн баганыг устгана (|> DROP column_name). -тэй тэнцэх SELECT * EXCEPT(column) стандарт SQL-д байдаг бөгөөд гаралтыг хялбарчлах эсвэл завсрын өгөгдлийн хэмжээг багасгахад ихэвчлэн ашиглагддаг.
• Хаана: Нөхцөлийг хангасан мөрүүдийг шүүдэг (|> WHERE condition). Энэ нь нэгтгэхээс өмнө эсвэл дараа нь аль ч хэсэгт гарч ирж, HAVING эсвэл QUALIFY гэх мэт өөр түлхүүр үгсийн хэрэгцээг арилгадаг.
• НЭГДСЭН: Бүтэн хүснэгт эсвэл бүлэглэсэн нэгтгэлийг гүйцэтгэдэг (|> AGGREGATE SUM(column) AS total GROUP BY category). SELECT доторх GROUP BY болон нэгтгэх функцуудыг орлуулж, синтаксийг хялбарчилж, үйлдлийн логик дараалалд нийцүүлнэ.
• ЗАХИАЛАХ: Мөрүүдийг өсөх эсвэл буурах дарааллаар эрэмбэлэх (|> ORDER BY column DESC). гэх мэт операторууд дагаж болох эрэмбэлэгдсэн үр дүнгийн хүснэгтийг гаргадаг LIMIT.
• ХЯЗГААР: Үр дүнгийн мөрийн тоог хязгаарладаг (|> LIMIT 10). Дараа нь ажиллана ORDER BY эсвэл шүүлтүүр болон нэгтгэлүүдийн байгалийн дарааллыг хадгалан дараалалгүй хүснэгтүүд дээр.
• Дуудлага хийх: Одоогийн хүснэгтийг оролт болгон ашиглан хүснэгтийн үнэ цэнэтэй функц эсвэл машин сургалтын загварыг гүйцэтгэдэг (|> CALL ML.PREDICT(MODEL project.model_name)). Шугаман, уншигдахуйц ажлын урсгалыг бий болгож, үүрлэсэн функцийн дуудлагын хэрэгцээг арилгана.
• СОНГОХ: Гаралтад оруулах баганын эцсийн төсөөллийг тодорхойлно (|> SELECT column1, column2). Төгсгөлийнхтэй адил дамжуулах хоолойд хаалтын үйл ажиллагааны үүрэг гүйцэтгэдэг SELECT стандарт SQL асуулгад.

Хоолойн синтакс нь SQL логикийг хялбарчилж, асуулгыг илүү цэвэр, хурдан ажиллахад хүргэдэг хамгийн түгээмэл хувилбаруудыг доор харуулав.

Дэд асуулгагүйгээр өгөгдлийг нэгтгэх

Нэгтгэх нь SQL-г дотроос нь мэдэрч эхэлдэг газар юм. Хэрэв та ямар нэг зүйлийг тоолохыг хүсвэл тэр тоонуудыг тоол, чи гэнэт хаалтанд байгаа тамын дотор байна.

SELECT c_count, COUNT(*) AS custdist
FROM (
  SELECT c_custkey, COUNT(o_orderkey) AS c_count
  FROM customer
  JOIN orders ON c_custkey = o_custkey
  WHERE o_comment NOT LIKE '%unusual%packages%'
  GROUP BY c_custkey
)
GROUP BY c_count
ORDER BY custdist DESC;

FROM customer
|> JOIN orders ON c_custkey = o_custkey
   AND o_comment NOT LIKE '%unusual%packages%'
|> AGGREGATE COUNT(o_orderkey) AS c_count GROUP BY c_custkey
|> AGGREGATE COUNT(*) AS custdist GROUP BY c_count
|> ORDER BY custdist DESC;

Үүрлэх, давхар бүлэглэх шаардлагагүй. Алхам бүр нь логикоор урсдаг бөгөөд асуулгыг бүхэлд нь дахин бичихгүйгээр бие даан өөрчилж болно.

Багануудыг алхам алхмаар цэвэрлэж, өөрчлөх

Хэрэв та текстийг жижиг үсгээр бичиж, нийт дүнг тооцоолж, нэмэлт багана буулгах шаардлагатай бол стандарт SQL таныг олон дахин бичихийг албаддаг. SELECT мэдэгдэл. Хоолойн синтаксийг танилцуулж байна SET, EXTENDБолон DROP операторуудын тусламжтайгаар та өөрчлөлтийг дарааллаар нь хэрэгжүүлэх боломжтой.

SELECT o_custkey, ROUND(o_totalprice) AS total_price
FROM (
  SELECT
    o_custkey,
    o_totalprice,
    LOWER(o_orderstatus) AS o_orderstatus
  FROM orders
)
WHERE total_price > 1000;

FROM orders
|> SET o_orderstatus = LOWER(o_orderstatus)
|> EXTEND ROUND(o_totalprice) AS total_price
|> WHERE total_price > 1000
|> SELECT o_custkey, total_price;

Үйлдэл бүр нь өмнөх үйлдлүүд дээр тулгуурладаг бөгөөд энэ нь өөрчлөлтийг хянах, логикийг дахин ашиглахад хялбар болгодог.

"ХАВИНГ"-ыг санахгүйгээр нэгтгэсний дараа шүүх

SQL-ийн нэг хачирхалтай зүйл бол шүүлтүүрийн цаг нь заалтаас хамаарч өөрчлөгддөг. Та ашигладаг WHERE бүлэглэхээс өмнө болон HAVING дараа, гэхдээ бодит байдал дээр хоёулаа мөрүүдийг шүүдэг. Хоолойн синтакс нь танд ашиглах боломжийг олгодог WHERE Та үүнийг хаана ч хамаагүй, тогтмол.

SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE active = TRUE
GROUP BY department
HAVING COUNT(*) > 5;

FROM employees
|> WHERE active = TRUE
|> AGGREGATE COUNT(*) AS emp_count GROUP BY department
|> WHERE emp_count > 5;

Одоо та шүүлтүүрийг бодож байгаа дарааллаар нь бичиж болно: эхлээд өгөгдлийг шүүж, дараа нь бүлэглээд дараа нь үр дүнг дахин шүүнэ.

Түр хүснэгтгүйгээр асуулга дибаг хийх

Стандарт SQL-д завсрын үр дүнг шалгахын тулд түр зуурын хүснэгт үүсгэх эсвэл кодыг олон CTE-д оруулах шаардлагатай. Хоолойн синтаксийн тусламжтайгаар та асуулга дамжуулах хоолойн аль ч цэг хүртэл ажиллуулж болно.

WITH filtered AS (
  SELECT * FROM orders WHERE o_totalprice > 500
),
summed AS (
  SELECT o_custkey, SUM(o_totalprice) AS total
  FROM filtered GROUP BY o_custkey
)
SELECT * FROM summed WHERE total > 10000;

FROM orders
|> WHERE o_totalprice > 500
|> AGGREGATE SUM(o_totalprice) AS total GROUP BY o_custkey
|> WHERE total > 10000;

Асуулгын угтвар бүрийг бие даан гүйцэтгэх боломжтой бөгөөд энэ нь та ямар ч үе шатанд өгөгдөлд "шалгах" боломжтой гэсэн үг юм. Энэ нь дибаг хийх, давтах илүү цэвэр, интерактив арга юм.

Загварууд ба функцуудыг үүрлэхгүйгээр гинжлэх

Хүснэгтээр үнэлэгдсэн функцууд эсвэл BigQuery ML загваруудтай ажиллах үед үүрлэх нь хурдан унших боломжгүй болно. Хоолойн синтакс нь эдгээр үүрлэсэн дуудлагуудыг CALL ашиглан шугаман хэлхээгээр сольдог.

SELECT *
FROM ML.PREDICT(
  MODEL `project.sentiment_model`,
  (SELECT text FROM reviews)
);

SELECT text FROM reviews
|> CALL ML.PREDICT(MODEL `project.sentiment_model`);

Хэрэв та хэд хэдэн загвар эсвэл хувиргалт хийвэл нэмэлт CALL мөрүүдийг давхарлана - хаалт шаардлагагүй.

Сонголтгүй өгөгдлийг эргүүлэх

Өгөгдлийг эргүүлэх нь ямагт уйтгартай процесс байсаар ирсэн бөгөөд ихэвчлэн давхар асуулга шаарддаг. Хоолойн синтакс нь үүнийг нэг урсгалын дараалал болгон хялбаршуулдаг.

SELECT *
FROM (
  SELECT n_name, c_acctbal, c_mktsegment
  FROM customer JOIN nation USING (n_nationkey)
)
PIVOT(SUM(c_acctbal) FOR n_name IN ('PERU', 'KENYA', 'JAPAN'));

FROM customer
|> JOIN nation USING (n_nationkey)
|> SELECT n_name, c_acctbal, c_mktsegment
|> PIVOT(SUM(c_acctbal) FOR n_name IN ('PERU', 'KENYA', 'JAPAN'));

Асуулга нь одоо түүх шиг уншиж байна: өгөгдлөөсөө эхэлж, түүнд нэгдэж, хэрэгтэй зүйлээ сонгоод дараа нь эргүүл.

Хоолойн синтакс яагаад тоглоомыг өөрчилдөг вэ?

Хоолойн синтакс нь SQL-г дахин зохион бүтээдэггүй - энэ нь үүнийг тодорхой болгодог. Энэ нь SQL-ийн тунхаглалын бүтцэд хамаатай бүхнийг хадгалдаг боловч логикийг ухрааж бичих танин мэдэхүйн ачааллыг арилгадаг.

Мэдээллийн судлаачид, шинжээчид, инженерүүдийн хувьд энэ нь:

• Асуултуудыг унших, бичих, дибаг хийхэд хялбар
• Энгийн даалгаврын хувьд дэд асуулга эсвэл CTE-г жонглёрлохоо больсон
• Таны хэрхэн бодож байгааг харуулсан оновчтой логик
• Гүйцэтгэл сайжирч, илүүц ажиллагаа багасна

BigQuery-ийн хоолойн синтакс нь орчин үеийн өгөгдлийн эрин үеийн SQL бөгөөд шугаман, мэдрэмжтэй, бодит ертөнцийн аналитикт оновчтой болсон.

холбоотой Martech Zone Агуулга

GA4 ашиглан Universal Analytics-ийн түүхэн өгөгдлийг хэрхэн нөөцлөх, тайлагнах вэ?

BigQuery: Demo PII нэр, хаяг, имэйл болон утасны дугаарыг үүсгэхийн тулд SQL-г хэрхэн ашиглах вэ

Суперметрүүд: Бизнесийн өсөлтөд маркетингийн өгөгдлийг задлах, автоматжуулах, хувиргах, идэвхжүүлэх

Google Analytics 4: Хэрэглэгчийн шинж чанар болон хүн ам зүйн өгөгдлийг ашиглан ойлголтыг хэрхэн нэмэгдүүлэх вэ