16 এসকিউএল কৌশল প্রতিটি শিক্ষানবিস জানা প্রয়োজন

1 থেকে 10 পর্যন্ত স্কেলে আপনার ডেটা গুদামজাত করার দক্ষতা কতটা ভালো?

7/10 এর উপরে যেতে চান? এই নিবন্ধটি তাহলে আপনার জন্য.

আপনার এসকিউএল কতটা ভালো? যত তাড়াতাড়ি সম্ভব একটি চাকরির ইন্টারভিউয়ের জন্য প্রস্তুত হতে চান?

এই ব্লগ পোস্টটি বিস্তারিতভাবে সবচেয়ে জটিল ডেটা গুদাম SQL কৌশল ব্যাখ্যা করে। এই বিষয়ে কিছু চিন্তাভাবনা লিখতে আমি BigQuery স্ট্যান্ডার্ড SQL উপভাষা ব্যবহার করব।

1. ইনক্রিমেন্টাল টেবিল এবং মার্জ

টেবিল আপডেট করা গুরুত্বপূর্ণ। এটা সত্যিই গুরুত্বপূর্ণ. আদর্শ পরিস্থিতি হল যখন আপনার লেনদেন হয় যা একটি প্রাথমিক কী, অনন্য পূর্ণসংখ্যা এবং স্বয়ংক্রিয় বৃদ্ধি। এই ক্ষেত্রে টেবিল আপডেট সহজ:

আধুনিক ডেটা গুদামগুলিতে অস্বাভাবিক স্টার-স্কিমা ডেটাসেটগুলির সাথে কাজ করার সময় এটি সর্বদা হয় না। আপনাকে SQL এর সাথে সেশন তৈরি করার এবং/অথবা ক্রমবর্ধমানভাবে ডেটাসেটগুলিকে শুধুমাত্র ডেটার একটি অংশ দিয়ে আপডেট করার দায়িত্ব দেওয়া হতে পারে। transaction_id বিদ্যমান নাও থাকতে পারে কিন্তু পরিবর্তে আপনাকে ডেটা মডেলের সাথে মোকাবিলা করতে হবে যেখানে অনন্য কী নির্ভর করে সর্বশেষ transaction_id (বা টাইমস্ট্যাম্প) পরিচিত। উদাহরণস্বরূপ, last_online ডেটাসেটের user_id সর্বশেষ পরিচিত সংযোগ টাইমস্ট্যাম্পের উপর নির্ভর করে। এই ক্ষেত্রে আপনি বিদ্যমান ব্যবহারকারীদের update করতে এবং নতুনদের insert চান।

মার্জ এবং ইনক্রিমেন্টাল আপডেট

আপনি MERGE ব্যবহার করতে পারেন অথবা আপনি অপারেশনটিকে দুটি ক্রিয়াতে বিভক্ত করতে পারেন। একটি বিদ্যমান রেকর্ডগুলিকে নতুনের সাথে আপডেট করার জন্য এবং একটি সম্পূর্ণ নতুনগুলি সন্নিবেশ করান যা প্রস্থান করে না (বাম যোগদানের পরিস্থিতি)।

MERGE হল একটি বিবৃতি যা সাধারণত রিলেশনাল ডাটাবেসে ব্যবহৃত হয়। Google BigQuery MERGE কমান্ড হল ডেটা ম্যানিপুলেশন ল্যাঙ্গুয়েজ (DML) স্টেটমেন্টগুলির মধ্যে একটি। এটি প্রায়শই একটি একক বিবৃতিতে পারমাণবিকভাবে তিনটি প্রধান কার্য সম্পাদন করতে ব্যবহৃত হয়। এই ফাংশনগুলি হল UPDATE, INSERT, এবং DELETE.

• দুই বা ততোধিক ডেটা মিলে গেলে UPDATE বা DELETE clause ব্যবহার করা যেতে পারে।
• INSERT ক্লজ ব্যবহার করা যেতে পারে যখন দুই বা ততোধিক ডেটা আলাদা হয় এবং মেলে না।
• UPDATE বা DELETE ক্লজটিও ব্যবহার করা যেতে পারে যখন প্রদত্ত ডেটা উৎসের সাথে মেলে না।

এর মানে হল যে Google BigQuery MERGE কমান্ড আপনাকে আপনার Google BigQuery টেবিল থেকে ডেটা আপডেট, সন্নিবেশ এবং মুছে Google BigQuery ডেটা মার্জ করতে সক্ষম করে৷

এই SQL বিবেচনা করুন:

2. শব্দ গণনা

UNNEST() করছেন এবং আপনার প্রয়োজনীয় শব্দটি তালিকায় আছে কিনা তা পরীক্ষা করুন আপনার প্রয়োজন অনেক পরিস্থিতিতে, যেমন ডেটা গুদাম অনুভূতি বিশ্লেষণে উপযোগী হতে পারে:

3. SELECT স্টেটমেন্টের বাইরে IF() স্টেটমেন্ট ব্যবহার করা

এটি আমাদের কোডের কিছু লাইন সংরক্ষণ করার এবং কোড-ভিত্তিক আরও বাগ্মী হওয়ার সুযোগ দেয়। সাধারণত আপনি এটিকে একটি সাব-ক্যোয়ারীতে রাখতে চান এবং যেখানে ক্লজে একটি ফিল্টার যোগ করতে চান তবে আপনি পরিবর্তে এটি করতে পারেন:

আরেকটি উদাহরণ কিভাবে পার্টিশন করা টেবিলের সাথে এটি ব্যবহার করবেন না । এটা করবেন না । এটি একটি খারাপ উদাহরণ কারণ ম্যাচিং টেবিলের প্রত্যয়গুলি সম্ভবত গতিশীলভাবে নির্ধারিত হয় (আপনার টেবিলের কিছুর উপর ভিত্তি করে) আপনাকে একটি সম্পূর্ণ টেবিল স্ক্যানের জন্য চার্জ করা হবে।

আপনি এটিকে HAVING clause এবং AGGREGATE ফাংশনেও ব্যবহার করতে পারেন।

4. ROLLUP দ্বারা GROUP ব্যবহার করা

ROLLUP ফাংশনটি একাধিক স্তরে একত্রীকরণ করতে ব্যবহৃত হয়। যখন আপনাকে মাত্রা গ্রাফের সাথে কাজ করতে হবে তখন এটি কার্যকর।

নিম্নলিখিত ক্যোয়ারীটি লেনদেনের ধরন (is_gift) দ্বারা প্রতিদিন মোট ক্রেডিট খরচ ফেরত দেয় যেখানে ধারায় উল্লেখ করা হয়েছে, এবং এটি প্রতিটি দিনের জন্য মোট ব্যয় এবং উপলব্ধ সমস্ত তারিখে মোট ব্যয়ও দেখায়।

5. টেবিলকে JSON-এ রূপান্তর করুন

কল্পনা করুন যে আপনাকে আপনার টেবিলটিকে JSON অবজেক্টে রূপান্তর করতে হবে যেখানে প্রতিটি রেকর্ড নেস্টেড অ্যারের একটি উপাদান। এখানেই to_json_string() ফাংশন দরকারী হয়ে ওঠে:

তারপর আপনি এটি যেকোনো জায়গায় ব্যবহার করতে পারেন: তারিখ, মার্কেটিং ফানেল, সূচক, হিস্টোগ্রাম গ্রাফ ইত্যাদি।

6. PARTITION BY ব্যবহার করে

দেওয়া user_id , date এবং total_cost কলাম। প্রতিটি তারিখের জন্য, সমস্ত সারি রাখার সময় আপনি কীভাবে প্রতিটি গ্রাহকের জন্য মোট আয়ের মান দেখাবেন? আপনি এইভাবে এটি অর্জন করতে পারেন:

7. চলমান গড়

প্রায়শই BI ডেভেলপারদের তাদের রিপোর্ট এবং চমত্কার ড্যাশবোর্ডে একটি চলমান গড় যোগ করার দায়িত্ব দেওয়া হয়। এটি 7, 14, 30 দিন/মাস বা এমনকি বছরের এমএ লাইন গ্রাফ হতে পারে। সুতরাং আমরা এটা কিভাবে করব?

8. তারিখ অ্যারে

আপনি যখন ব্যবহারকারী ধরে রাখার সাথে কাজ করেন বা অনুপস্থিত মান, যেমন তারিখগুলির জন্য কিছু ডেটাসেট পরীক্ষা করতে চান তখন সত্যিই সহজ হয়ে ওঠে। BigQuery নামক একটি ফাংশন আছে GENERATE_DATE_ARRAY :

9. সারি_সংখ্যা()

এটি আপনার ডেটা থেকে সাম্প্রতিক কিছু পেতে, যেমন সর্বশেষ আপডেট হওয়া রেকর্ড, ইত্যাদি বা এমনকি সদৃশগুলি সরাতেও দরকারী:

10. NTIL()

আরেকটি নম্বরিং ফাংশন। আপনার মোবাইল অ্যাপ থাকলে Login duration in seconds মতো জিনিসগুলি নিরীক্ষণ করতে সত্যিই দরকারী। উদাহরণস্বরূপ, আমার অ্যাপ Firebase-এর সাথে সংযুক্ত আছে এবং ব্যবহারকারীরা login করলে আমি দেখতে পারি যে তাদের জন্য কত সময় লেগেছে।

এই ফাংশন সারিগুলিকে সারি ক্রমানুসারের উপর ভিত্তি করে constant_integer_expression বাকেটগুলিতে বিভক্ত করে এবং প্রতিটি সারিতে নির্ধারিত 1-ভিত্তিক বালতি নম্বর প্রদান করে। বালতিতে সারির সংখ্যা সর্বাধিক 1 দ্বারা পৃথক হতে পারে। অবশিষ্ট মান (বালতি দ্বারা বিভক্ত সারির অবশিষ্ট সংখ্যা) প্রতিটি বালতির জন্য একটি করে বিতরণ করা হয়, বালতি 1 দিয়ে শুরু হয়। যদি constant_integer_expression এর মূল্যায়ন NULL, 0 বা ঋণাত্মক হয়, একটি ত্রুটি প্রদান করা হয়।

11. র‍্যাঙ্ক / ঘনত্ব

এগুলোকে নাম্বারিং ফাংশনও বলা হয়। আমি ডিফল্ট র‌্যাঙ্কিং ফাংশন হিসাবে DENSE_RANK ব্যবহার করার প্রবণতা রাখি কারণ এটি পরবর্তী উপলব্ধ র‌্যাঙ্কিং এড়িয়ে যায় না যেখানে RANK হবে। এটি পরপর র‍্যাঙ্ক মান প্রদান করে। আপনি এটি একটি পার্টিশনের সাথে ব্যবহার করতে পারেন যা ফলাফলগুলিকে স্বতন্ত্র বালতিতে ভাগ করে। প্রতিটি পার্টিশনের সারি একই র‌্যাঙ্ক পায় যদি তাদের মান একই থাকে। উদাহরণ:

পণ্যের দাম সহ আরেকটি উদাহরণ:

12. পিভট/আনপিভট

পিভট সারিগুলিকে কলামে পরিবর্তন করে। এটা সব এটা করে. আনপিভট বিপরীত করে।

13. প্রথম_মান / শেষ_মান

এটি আরেকটি দরকারী ফাংশন যা সেই নির্দিষ্ট পার্টিশনের প্রথম / শেষ মানের বিপরীতে প্রতিটি সারির জন্য একটি ডেল্টা পেতে সহায়তা করে।

14. একটি টেবিলকে অ্যারে অফ স্ট্রাকটে রূপান্তর করুন এবং সেগুলিকে UDF-এ পাস করুন

এটি উপযোগী যখন আপনাকে প্রতিটি সারি বা একটি টেবিলে কিছু জটিল যুক্তি সহ একটি ব্যবহারকারী সংজ্ঞায়িত ফাংশন (UDF) প্রয়োগ করতে হবে। আপনি সর্বদা আপনার টেবিলকে TYPE STRUCT অবজেক্টের একটি অ্যারে হিসাবে বিবেচনা করতে পারেন এবং তারপর তাদের প্রতিটিকে UDF-এ পাস করুন৷ এটা আপনার যুক্তির উপর নির্ভর করে। উদাহরণস্বরূপ, আমি ক্রয়ের মেয়াদ শেষ হওয়ার সময় গণনা করতে এটি ব্যবহার করি:

একইভাবে আপনি UNION ALL ব্যবহার করার প্রয়োজন ছাড়াই টেবিল তৈরি করতে পারেন। উদাহরণস্বরূপ, আমি ইউনিট পরীক্ষার জন্য কিছু পরীক্ষার ডেটা উপহাস করতে এটি ব্যবহার করি। এইভাবে আপনি আপনার সম্পাদকে Alt + Shift + Down ব্যবহার করে খুব দ্রুত এটি করতে পারেন।

15. অনুসরণ করা এবং সীমাহীন অনুসরণ ব্যবহার করে ইভেন্ট ফানেল তৈরি করা

ভালো উদাহরণ মার্কেটিং ফানেল হতে পারে। আপনার ডেটাসেটে একই ধরণের ইভেন্টগুলি ক্রমাগত পুনরাবৃত্তি হতে পারে তবে আদর্শভাবে আপনি প্রতিটি ইভেন্টকে পরবর্তী একটি ভিন্ন ধরণের সাথে চেইন করতে চান। এটি উপযোগী হতে পারে যখন আপনাকে একটি ফানেল ডেটাসেট তৈরি করার জন্য কোনো কিছুর তালিকা, যেমন ইভেন্ট, কেনাকাটা ইত্যাদির প্রয়োজন হয়। PARTITION BY এর সাথে কাজ করা আপনাকে সমস্ত ফলোওয়াইং ইভেন্টগুলিকে গোষ্ঠীভুক্ত করার সুযোগ দেয়, প্রতিটি পার্টিশনের মধ্যে কতগুলিই থাকুক না কেন।

16. Regexp

আপনি যদি অসংগঠিত ডেটা, যেমন এফএক্স রেট, কাস্টম গ্রুপিং ইত্যাদি থেকে কিছু বের করতে চান তবে আপনি এটি ব্যবহার করবেন।

regexp ব্যবহার করে মুদ্রা বিনিময় হার নিয়ে কাজ করা

বিনিময় হার ডেটা সহ এই উদাহরণটি বিবেচনা করুন:

regexp ব্যবহার করে অ্যাপ সংস্করণের সাথে কাজ করা

কখনও কখনও আপনি আপনার অ্যাপের জন্য প্রধান , প্রকাশ বা মড সংস্করণ পেতে এবং একটি কাস্টম প্রতিবেদন তৈরি করতে regexp ব্যবহার করতে চাইতে পারেন:

উপসংহার

এসকিউএল একটি শক্তিশালী টুল যা ডেটা ম্যানিপুলেট করতে সাহায্য করে। আশা করি ডিজিটাল মার্কেটিং থেকে এসকিউএল ব্যবহারের ক্ষেত্রে আপনার কাজে লাগবে। এটি সত্যিই একটি সহজ দক্ষতা এবং অনেক প্রকল্পে আপনাকে সাহায্য করতে পারে। এই SQL স্নিপেটগুলি আমার জীবনকে অনেক সহজ করে তুলেছে এবং আমি প্রায় প্রতিদিনই কাজে ব্যবহার করি। আরও, এসকিউএল এবং আধুনিক ডেটা গুদামগুলি ডেটা বিজ্ঞানের জন্য প্রয়োজনীয় সরঞ্জাম। এর শক্তিশালী উপভাষা বৈশিষ্ট্যগুলি সহজেই ডেটা মডেল এবং কল্পনা করতে দেয়। যেহেতু SQL হল সেই ভাষা যা ডেটা গুদাম এবং ব্যবসায়িক বুদ্ধিমত্তা পেশাদাররা ব্যবহার করে, আপনি যদি তাদের সাথে ডেটা ভাগ করতে চান তবে এটি একটি চমৎকার নির্বাচন। এটি বাজারে প্রায় প্রতিটি ডেটা গুদাম/লেক সমাধানের সাথে যোগাযোগ করার সবচেয়ে সাধারণ উপায়।

মূলত ডেটামাইক দ্বারা mydataschool.com এ প্রকাশিত

মাইক হলেন একজন আবেগী এবং ডিজিটালভাবে ফোকাসড ব্যক্তি যার প্রচুর ড্রাইভ এবং উদ্যম রয়েছে, ডিজিটাল মার্কেটিং এর সম্পূর্ণ মিশ্রিত চ্যালেঞ্জগুলিকে ভালবাসে। যুক্তরাজ্যে থাকেন, 2015 সালে নিউক্যাসল বিশ্ববিদ্যালয় থেকে এমবিএ সম্পন্ন করেছেন।