تعلم هذه الحيل السريعة في PostgreSQL

تعد PostgreSQL واحدة من أشهر لهجات SQL مفتوحة المصدر. تتمثل إحدى مزاياها الرئيسية في القدرة على توسيع وظائفها باستخدام بعض الأدوات المدمجة.

هنا ، دعنا نلقي نظرة على بعض حيل PostgreSQL التي يمكنك البدء في استخدامها لنقل مهاراتك في SQL إلى المستوى التالي.

سوف تتعلم كيفية:

  • انسخ الملفات بسرعة إلى قاعدة بيانات
  • تلخيص البيانات في شكل جدولي
  • استفد من المصفوفات وبيانات JSON في SQL
  • العمل مع البيانات الهندسية
  • قم بتشغيل التحليلات الإحصائية مباشرة على قاعدة البيانات الخاصة بك
  • استخدم العودية لحل المشاكل

انسخ البيانات من ملف

طريقة سهلة لاستيراد البيانات بسرعة من ملف خارجي هي استخدام وظيفة النسخ. ما عليك سوى إنشاء الجدول الذي تريد استخدامه ، ثم تمرير مسار ملف مجموعة البيانات الخاصة بك إلى الأمر COPY.

ينشئ المثال أدناه جدولاً يسمى الإيرادات ويملأها من ملف CSV تم إنشاؤه عشوائيًا.

يمكنك تضمين معلمات إضافية ، للإشارة إلى نوع الملف (هنا ، الملف بتنسيق CSV) وما إذا كنت تريد قراءة الصف الأول كرؤوس أعمدة.

يمكنك معرفة المزيد هنا.

CREATE TABLE revenue ( store VARCHAR, year INT, revenue INT, PRIMARY KEY (product, year) ); COPY revenue FROM '~/Projects/datasets/revenue.csv' WITH HEADER CSV;

تلخيص البيانات باستخدام وظيفة الجدول الترافقي

إذا كنت تتخيل نفسك كمحترف جداول بيانات ، فمن المحتمل أن تكون على دراية بإنشاء جداول محورية من عمليات تفريغ البيانات. يمكنك فعل الشيء نفسه في PostgreSQL باستخدام وظيفة الجدول الترافقي.

يمكن أن تأخذ الدالة الجدلية البيانات في النموذج الموجود على اليسار وتلخصها في النموذج الموجود على اليمين (وهو أسهل كثيرًا في القراءة). سيتبع المثال هنا بيانات الإيرادات من قبل.

أولاً ، قم بتمكين ملحق tablefunc باستخدام الأمر أدناه:

CREATE EXTENSION tablefunc;

بعد ذلك ، اكتب استعلامًا باستخدام وظيفة الجدولة:

SELECT * FROM CROSSTAB( 'SELECT * FROM revenue ORDER BY 1,2' ) AS summary( store VARCHAR, "2016" INT, "2017" INT, "2018" INT );

هناك شيئان يجب مراعاتهما عند استخدام هذه الوظيفة.

  • أولاً ، قم بتمرير استعلام لتحديد البيانات من الجدول الأساسي. يمكنك ببساطة اختيار الجدول كما هو (كما هو موضح هنا). ومع ذلك ، قد ترغب في التصفية أو الانضمام أو التجميع إذا لزم الأمر. تأكد من طلب البيانات بشكل صحيح.
  • بعد ذلك ، حدد الإخراج (في المثال ، يسمى الإخراج "ملخص" ، ولكن يمكنك تسميته بأي اسم). ضع قائمة برؤوس الأعمدة التي تريد استخدامها ونوع البيانات التي ستحتوي عليها.

سيكون الإخراج كما هو موضح أدناه:

 store | 2016 | 2017 | 2018 ---------+---------+---------+--------- Alpha | 1637000 | 2190000 | 3287000 Bravo | 2205000 | 982000 | 3399000 Charlie | 1549000 | 1117000 | 1399000 Delta | 664000 | 2065000 | 2931000 Echo | 1795000 | 2706000 | 1047000 (5 rows)

العمل مع المصفوفات و JSON

تدعم PostgreSQL أنواع بيانات المصفوفات متعددة الأبعاد. يمكن مقارنتها بأنواع البيانات المماثلة في العديد من اللغات الأخرى ، بما في ذلك Python و JavaScript.

قد ترغب في استخدامها في المواقف التي تساعد فيها على العمل مع بيانات أكثر ديناميكية وأقل تنظيماً.

على سبيل المثال ، تخيل جدولاً يصف المقالات المنشورة وعلامات الموضوع. لا يمكن أن تحتوي المقالة على علامات ، أو قد تحتوي على العديد من العلامات. ستكون محاولة تخزين هذه البيانات في تنسيق جدول منظم أمرًا معقدًا بلا داعٍ.

يمكنك تحديد المصفوفات باستخدام نوع البيانات ، متبوعًا بأقواس مربعة. يمكنك تحديد أبعادها اختياريًا (ومع ذلك ، لا يتم فرض ذلك).

على سبيل المثال ، لإنشاء مصفوفة 1-D لأي عدد من عناصر النص ، يمكنك استخدام text[]. لإنشاء مصفوفة ثلاثية الأبعاد ثنائية الأبعاد لعناصر عدد صحيح ، يمكنك استخدام int[3][3].

ألق نظرة على المثال أدناه:

CREATE TABLE articles ( title VARCHAR PRIMARY KEY, tags TEXT[] );

لإدراج المصفوفات كسجلات ، استخدم بناء الجملة '{"first","second","third"}'.

INSERT INTO articles (title, tags) VALUES ('Lorem ipsum', '{"random"}'), ('Placeholder here', '{"motivation","random"}'), ('Postgresql tricks', '{"data","self-reference"}');

هناك الكثير من الأشياء التي يمكنك فعلها باستخدام المصفوفات في PostgreSQL.

كبداية ، يمكنك التحقق مما إذا كانت المصفوفة تحتوي على عنصر معين. هذا مفيد للتصفية. يمكنك استخدام عامل التشغيل "يحتوي على" @>للقيام بذلك. يعثر الاستعلام أدناه على جميع المقالات التي تحمل علامة "عشوائي".

SELECT * FROM articles WHERE tags @> '{"random"}';

يمكنك أيضًا تجميع (ضم معًا) المصفوفات باستخدام ||عامل التشغيل ، أو التحقق من وجود عناصر متداخلة مع &&عامل التشغيل.

يمكنك البحث في المصفوفات عن طريق الفهرس (على عكس العديد من اللغات ، تبدأ مصفوفات PostgreSQL في العد من واحدة ، بدلاً من الصفر).

SELECT tags[1] FROM articles;

بالإضافة إلى المصفوفات ، تتيح لك PostgreSQL أيضًا استخدام JSON كنوع بيانات. مرة أخرى ، يوفر هذا مزايا العمل مع البيانات غير المهيكلة. يمكنك أيضًا الوصول إلى العناصر من خلال اسمها الرئيسي.

CREATE TABLE sessions ( session_id SERIAL PRIMARY KEY, session_info JSON ); INSERT INTO sessions (session_info) VALUES ('{"app_version": 1.0, "device_type": "Android"}'), ('{"app_version": 1.2, "device_type": "iOS"}'), ('{"app_version": 1.4, "device_type": "iOS", "mode":"default"}');

مرة أخرى ، هناك العديد من الأشياء التي يمكنك القيام بها باستخدام بيانات JSON في PostgreSQL. يمكنك استخدام عوامل التشغيل ->و ->>"تفريغ" كائنات JSON لاستخدامها في الاستعلامات.

على سبيل المثال ، يبحث هذا الاستعلام عن قيم device_typeالمفتاح:

SELECT session_info -> 'device_type' AS devices FROM sessions;

ويحسب هذا الاستعلام عدد الجلسات التي كانت في الإصدار 1.0 من التطبيق أو إصدار سابق:

SELECT COUNT(*) FROM sessions WHERE CAST(session_info ->> 'app_version' AS decimal) <= 1.0;

قم بإجراء التحليلات الإحصائية

في كثير من الأحيان ، يرى الأشخاص أن SQL جيدة لتخزين البيانات وتشغيل الاستعلامات البسيطة ، ولكن ليس لإجراء المزيد من التحليلات المتعمقة. لذلك ، يجب عليك استخدام أداة أخرى مثل Python أو R أو برنامج جداول البيانات المفضل لديك.

ومع ذلك ، توفر PostgreSQL معها إمكانات إحصائية كافية لتبدأ بها.

على سبيل المثال ، يمكنه حساب ملخص الإحصائيات والارتباط والانحدار وأخذ العينات العشوائية. يحتوي الجدول أدناه على بعض البيانات البسيطة للتلاعب بها.

CREATE TABLE stats ( sample_id SERIAL PRIMARY KEY, x INT, y INT ); INSERT INTO stats (x,y) VALUES (1,2), (3,4), (6,5), (7,8), (9,10);

You can find the mean, variance and standard deviation using the functions below:

SELECT AVG(x), VARIANCE(x), STDDEV(x) FROM stats;

You can also find the median (or any other percentile value) using the percentile_cont function:

-- median SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) FROM stats; -- 90th percentile SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY x) FROM stats;

Another trick lets you calculate the correlation coefficients between different columns. Simply use the corr function.

SELECT CORR(x,y) FROM stats;

PostgreSQL lets you run linear regression (sometimes called the most basic form of machine learning) via a set of inbuilt functions.

SELECT REGR_INTERCEPT(x,y), REGR_SLOP(x,y), REGR_R2(x,y) FROM stats;

You can even run Monte Carlo simulations with single queries. The query below uses the generate_series and random number functions to estimate the value of π by randomly sampling one million points inside a unit circle.

SELECT CAST( COUNT(*) * 4 AS FLOAT ) / 1000000 AS pi FROM GENERATE_SERIES(1,1000000) WHERE CIRCLE(POINT(0.5,0.5),0.5) @> POINT(RANDOM(), RANDOM());

Work with shape data

Another unusual data type available in PostgreSQL is geometric data.

That's right, you can work with points, lines, polygons and circles within SQL.

Points are the basic building block for all geometric data types in PostgreSQL. They are represented as (x, y) coordinates.

SELECT POINT(0,0) AS "origin", POINT(1,1) AS "point";

You can also define lines. These can either be infinite lines (specified by giving any two points on the line). Or, they can be line segments (specified by giving the 'start' and 'end' points of the line).

SELECT LINE '((0,0),(1,1))' AS "line", LSEG '((2,2),(3,3))' AS "line_segment";

Polygons are defined by a longer series of points.

SELECT POLYGON '((0,0),(1,1),(0,2))' AS "triangle", POLYGON '((0,0),(0,1),(1,1),(1,0))' AS "square", POLYGON '((0,0),(0,1),(2,1),(2,0))' AS "rectangle";

Circles are defined by a central point and a radius.

SELECT CIRCLE '((0,0),1)' as "small_circle", CIRCLE '(0,0),5)' as "big_circle";

There are many functions and operators that can be applied to geometric data in PostgreSQL.

You can:

  • Check if two lines are parallel with the ?|| operator:
SELECT LINE '((0,0),(1,1))' ?|| LINE '((2,3),(3,4))';
  • Find the distance between two objects with the operator:
SELECT POINT(0,0)  POINT(1,1);
  • Check if two shapes overlap at any point with the && operator:
SELECT CIRCLE '((0,0),1)' && CIRCLE '((1,1),1)';
  • Translate (shift position) a shape using the + operator:
SELECT POLYGON '((0,0),(1,2),(1,1))' + POINT(0,3);

And lots more besides - check out the documentation for more detail!

Use recursive queries

Recursion is a programming technique that can be used to solve problems using a function which calls itself. Did you know that you can write recursive queries in PostgreSQL?

There are three parts required to do this:

  • First, you define a starting expression.
  • Then, define a recursive expression that will be evaluated repeatedly
  • Finally, define a "termination criteria" - a condition which tells the function to stop calling itself, and return an output.

The query below returns the first hundred numbers in the Fibonacci sequence:

WITH RECURSIVE fibonacci(n,x,y) AS ( SELECT 1 AS n , 0 :: NUMERIC AS x, 1 :: NUMERIC AS y UNION ALL SELECT n + 1 AS n, y AS x, x + y AS y FROM fibonacci WHERE n < 100 ) SELECT x FROM fibonacci;

Let's break this down.

First, it uses the WITH clause to define a (recursive) Common Table Expression called fibonacci. Then, it defines an initial expression:

WITH RECURSIVE fibonacci(n,x,y) AS ( SELECT 1 AS n , 0 :: NUMERIC AS x, 1 :: NUMERIC AS y...

بعد ذلك ، يحدد التعبير العودي الذي يستعلم عنه fibonacci:

 ...UNION ALL SELECT n + 1 AS n, y AS x, x + y AS y FROM fibonacci...

أخيرًا ، يستخدم جملة WHERE لتحديد معايير الإنهاء ، ثم يختار العمود x لإعطاء تسلسل الإخراج:

...WHERE n < 100 ) SELECT x FROM fibonacci;

ربما يمكنك التفكير في مثال آخر للتكرار يمكن تنفيذه في PostgreSQL؟

الملاحظات الختامية

إذن ، ها هي - نظرة سريعة على بعض الميزات الرائعة التي قد تكون تعرفها أو لا تعرفها عن PostgreSQL التي يمكن أن توفرها. ليس هناك شك في المزيد من الميزات التي تستحق التغطية والتي لم يتم إدراجها في هذه القائمة.

PostgreSQL هي لغة برمجة غنية وقوية في حد ذاتها. لذلك ، في المرة القادمة التي تواجه فيها مشكلة في معرفة كيفية حل مشكلة متعلقة بالبيانات ، ألق نظرة وشاهد ما إذا كانت PostgreSQL قد غطيت. قد تتفاجأ بعدد المرات التي يحدث فيها ذلك!

شكرا للقراءة!