Places Insights ডেটা অ্যাক্সেস করার জন্য, আপনাকে BigQuery-তে SQL কোয়েরি লিখতে হবে যা স্থান সম্পর্কে সমষ্টিগত অন্তর্দৃষ্টি প্রদান করবে। কোয়েরিতে উল্লেখিত অনুসন্ধানের মানদণ্ডের জন্য ডেটাসেট থেকে ফলাফলগুলি ফেরত পাঠানো হবে।
কোয়েরির মূল বিষয়গুলি
নিচের ছবিটি একটি কোয়েরির মৌলিক বিন্যাস দেখায়:
প্রশ্নের প্রতিটি অংশ নীচে আরও বিশদে বর্ণনা করা হয়েছে।
কোয়েরির প্রয়োজনীয়তা
কোয়েরিতে SELECT স্টেটমেন্টে WITH AGGREGATION_THRESHOLD অন্তর্ভুক্ত থাকতে হবে এবং ডেটাসেটটি নির্দিষ্ট করতে হবে। উদাহরণস্বরূপ:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places`
এই উদাহরণে, আপনি মার্কিন যুক্তরাষ্ট্রের জন্য places_insights___us.places ডেটাসেট নির্দিষ্ট করতে FROM ব্যবহার করেন।
একটি প্রকল্পের নাম উল্লেখ করুন (ঐচ্ছিক)
আপনি ঐচ্ছিকভাবে আপনার প্রকল্পের নাম কোয়েরিতে অন্তর্ভুক্ত করতে পারেন। যদি আপনি কোনও প্রকল্পের নাম নির্দিষ্ট না করেন, তাহলে আপনার কোয়েরিটি সক্রিয় প্রকল্পে ডিফল্ট হবে।
যদি আপনি বিভিন্ন প্রকল্পে একই নামের ডেটাসেট লিঙ্ক করে থাকেন, অথবা যদি আপনি সক্রিয় প্রকল্পের বাইরে কোনও টেবিল অনুসন্ধান করেন, তাহলে আপনি আপনার প্রকল্পের নাম অন্তর্ভুক্ত করতে চাইতে পারেন।
উদাহরণস্বরূপ, [project name].[dataset name].places ।
একটি সমষ্টি ফাংশন নির্দিষ্ট করুন
নিচের উদাহরণটি সমর্থিত BigQuery অ্যাগ্রিগেশন ফাংশনগুলি দেখায়। এই কোয়েরিটি নিউ ইয়র্ক সিটির এম্পায়ার স্টেট বিল্ডিংয়ের ১০০০ মিটার ব্যাসার্ধের মধ্যে অবস্থিত সমস্ত স্থানের রেটিংগুলিকে একত্রিত করে রেটিং পরিসংখ্যান তৈরি করে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(id) AS place_count, APPROX_COUNT_DISTINCT(rating) as distinct_ratings, COUNTIF(rating > 4.0) as good_rating_count, LOGICAL_AND(rating <= 5) as all_ratings_equal_or_below_five, LOGICAL_OR(rating = 5) as any_rating_exactly_five, AVG(rating) as avg_rating, SUM(user_rating_count) as rating_count, COVAR_POP(rating, user_rating_count) as rating_covar_pop, COVAR_SAMP(rating, user_rating_count) as rating_covar_samp, STDDEV_POP(rating) as rating_stddev_pop, STDDEV_SAMP(rating) as rating_stddev_samp, VAR_POP(rating) as rating_var_pop, VAR_SAMP(rating) as rating_var_samp, FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000) AND business_status = "OPERATIONAL"
একটি অবস্থান সীমাবদ্ধতা নির্দিষ্ট করুন
যদি আপনি কোনও অবস্থানের সীমাবদ্ধতা নির্দিষ্ট না করেন, তাহলে ডেটা একত্রিতকরণ সমগ্র ডেটাসেটে প্রয়োগ করা হবে। সাধারণত আপনি একটি নির্দিষ্ট এলাকা অনুসন্ধানের জন্য একটি অবস্থানের সীমাবদ্ধতা নির্দিষ্ট করেন, যেমনটি নীচে দেখানো হয়েছে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
এই উদাহরণে, আপনার কোয়েরিটি নিউ ইয়র্ক সিটির এম্পায়ার স্টেট বিল্ডিংকে কেন্দ্র করে একটি লক্ষ্য সীমাবদ্ধতা নির্দিষ্ট করে, যার ব্যাসার্ধ ১০০০ মিটার।
অনুসন্ধান এলাকা নির্দিষ্ট করার জন্য আপনি একটি বহুভুজ ব্যবহার করতে পারেন। বহুভুজ ব্যবহার করার সময়, বহুভুজের বিন্দুগুলিকে একটি বদ্ধ লুপ নির্ধারণ করতে হবে যেখানে বহুভুজের প্রথম বিন্দুটি শেষ বিন্দুর মতোই হবে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_CONTAINS(ST_GEOGFROMTEXT("""POLYGON((-73.985708 40.75773,-73.993324 40.750298, -73.9857 40.7484,-73.9785 40.7575, -73.985708 40.75773))"""), point)
পরবর্তী উদাহরণে, আপনি সংযুক্ত বিন্দুর একটি লাইন ব্যবহার করে অনুসন্ধান এলাকাটি সংজ্ঞায়িত করবেন। লাইনটি Routes API দ্বারা গণনা করা একটি ভ্রমণ রুটের অনুরূপ। রুটটি একটি যানবাহন, একটি সাইকেল, অথবা একজন পথচারীর জন্য হতে পারে:
DECLARE route GEOGRAPHY; SET route = ST_GEOGFROMTEXT("""LINESTRING(-73.98903537033028 40.73655649223003, -73.93580216278471 40.80955538843361)"""); SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(route, point, 100)
এই উদাহরণে, আপনি অনুসন্ধান ব্যাসার্ধটি লাইনের চারপাশে 100 মিটারে সেট করেছেন।
স্থান ডেটাসেট ক্ষেত্র অনুসারে ফিল্টার করুন
ডেটাসেট স্কিমা দ্বারা সংজ্ঞায়িত ক্ষেত্রগুলির উপর ভিত্তি করে আপনার অনুসন্ধানকে পরিমার্জন করুন। regular_opening_hours , price_level এবং গ্রাহক rating এর মতো ডেটাসেট ক্ষেত্রগুলির উপর ভিত্তি করে ফলাফল ফিল্টার করুন।
আপনার আগ্রহের দেশের জন্য ডেটাসেট স্কিমা দ্বারা সংজ্ঞায়িত ডেটাসেটের যেকোনো ক্ষেত্র উল্লেখ করুন। প্রতিটি দেশের জন্য ডেটাসেট স্কিমা দুটি অংশ নিয়ে গঠিত:
- সকল দেশের ডেটাসেটের জন্য সাধারণ মূল স্কিমা ।
- একটি দেশ-নির্দিষ্ট স্কিমা যা সেই দেশের জন্য নির্দিষ্ট স্কিমা উপাদানগুলিকে সংজ্ঞায়িত করে।
উদাহরণস্বরূপ, আপনার কোয়েরিতে একটি WHERE ধারা অন্তর্ভুক্ত থাকতে পারে যা কোয়েরির জন্য ফিল্টারিং মানদণ্ড নির্ধারণ করে। নিম্নলিখিত উদাহরণে, আপনি tourist_attraction ধরণের স্থানগুলির জন্য সমষ্টিগত ডেটা ফেরত পাঠান যার business_status OPERATIONAL এর সাথে থাকে, যার rating 4.0 এর চেয়ে বেশি বা সমান এবং allows_dogs true এ সেট করা থাকে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000) AND 'tourist_attraction' IN UNNEST(types) AND business_status = "OPERATIONAL" AND rating >= 4.0 AND allows_dogs = true
পরবর্তী কোয়েরিটি এমন জায়গাগুলির ফলাফল প্রদান করে যেখানে কমপক্ষে আটটি ইভি চার্জিং স্টেশন রয়েছে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ev_charge_options.connector_count > 8;
স্থানের প্রাথমিক ধরণ এবং স্থানের ধরণ অনুসারে ফিল্টার করুন
ডেটাসেটের প্রতিটি স্থানে থাকতে পারে:
Place types দ্বারা সংজ্ঞায়িত প্রকারগুলি থেকে এর সাথে যুক্ত একটি একক প্রাথমিক প্রকার । উদাহরণস্বরূপ, প্রাথমিক প্রকারটি
mexican_restaurantঅথবাsteak_houseহতে পারে। কোনও স্থানের প্রাথমিক প্রকারের ফলাফল ফিল্টার করতে একটি কোয়েরিতেprimary_typeব্যবহার করুন।Place types দ্বারা সংজ্ঞায়িত প্রকারগুলি থেকে এর সাথে সম্পর্কিত একাধিক প্রকারের মান । উদাহরণস্বরূপ, একটি রেস্তোরাঁর নিম্নলিখিত প্রকারগুলি থাকতে পারে:
seafood_restaurant,restaurant,food,point_of_interest,establishment। স্থানের সাথে সম্পর্কিত প্রকারের তালিকার ফলাফল ফিল্টার করতে একটি কোয়েরিতেtypesব্যবহার করুন।
নিম্নলিখিত কোয়েরিটি এমন সমস্ত জায়গার ফলাফল প্রদান করে যেখানে প্রাথমিক ধরণের skin_care_clinic রয়েছে এবং এটি একটি spa হিসেবেও কাজ করে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE 'spa' IN UNNEST(types) AND 'skin_care_clinic' = primary_type
স্থান আইডি অনুসারে ফিল্টার করুন
নিচের উদাহরণটি ৫টি স্থানের গড় রেটিং গণনা করে। স্থানগুলি তাদের place_id দ্বারা চিহ্নিত করা হয়।
DECLARE place_ids ARRAY<STRING>; SET place_ids = ['ChIJPQOh8YVZwokRE2WsbZI4tOk', 'ChIJibtT3ohZwokR7tX0gp0nG8U', 'ChIJdfD8moVZwokRO6vxjXAtoWs', 'ChIJsdNONuFbwokRLM-yuifjb8k', 'ChIJp0gKoClawokR0txqrcaEkFc']; SELECT WITH AGGREGATION_THRESHOLD AVG(rating) as avg_rating, FROM `PROJECT_NAME.places_insights___us.places`, UNNEST(place_ids) place_id WHERE id = place_id;
নির্দিষ্ট স্থানের আইডি ফিল্টার করুন
আপনি একটি কোয়েরি থেকে প্লেস আইডির একটি অ্যারেও বাদ দিতে পারেন।
আপনি যে প্লেস আইডিগুলি খুঁজছেন তা প্লেস আইডি ফাইন্ডার ব্যবহার করে খুঁজে পেতে পারেন, অথবা প্রোগ্রাম্যাটিকভাবে প্লেস এপিআই ব্যবহার করে টেক্সট সার্চ (নতুন) অনুরোধ সম্পাদন করতে পারেন ।
নিচের উদাহরণে, কোয়েরিটি অস্ট্রেলিয়ার সিডনির ২০০০ পোস্টাল কোডে ক্যাফের সংখ্যা খুঁজে বের করে, যা excluded_cafes অ্যারেতে দেখা যায় না । এই ধরনের কোয়েরি এমন একজন ব্যবসার মালিকের জন্য কার্যকর হতে পারে যারা তাদের নিজস্ব ব্যবসাকে গণনা থেকে বাদ দিতে চান।
WITH excluded_cafes AS ( -- List the specific place IDs to exclude from the final count SELECT * FROM UNNEST([ 'ChIJLTcYGz-uEmsRmazk9oMnP5w', 'ChIJeWDDDNOvEmsRF8SMPUwPbhw', 'ChIJKdaKHbmvEmsRSdxq_1O05bU' ]) AS place_id ) SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `places_insights___au.places` AS places -- Perform a LEFT JOIN to identify which places are in the exclusion list LEFT JOIN excluded_cafes ON places.id = excluded_cafes.place_id WHERE -- Filter for specific place type and postal code places.primary_type = 'cafe' AND '2000' IN UNNEST(places.postal_code_names) -- Keep only the rows where the join failed (meaning the ID was NOT in the list) AND excluded_cafes.place_id IS NULL;
পূর্বনির্ধারিত ডেটা মানের উপর ফিল্টার করুন
অনেক ডেটাসেট ক্ষেত্রের পূর্বনির্ধারিত মান থাকে। উদাহরণস্বরূপ
price_levelক্ষেত্রটি নিম্নলিখিত পূর্বনির্ধারিত মানগুলিকে সমর্থন করে:-
PRICE_LEVEL_FREE -
PRICE_LEVEL_INEXPENSIVE -
PRICE_LEVEL_MODERATE -
PRICE_LEVEL_EXPENSIVE -
PRICE_LEVEL_VERY_EXPENSIVE
-
business_statusক্ষেত্রটি নিম্নলিখিত পূর্বনির্ধারিত মানগুলিকে সমর্থন করে:-
OPERATIONAL -
CLOSED_TEMPORARILY -
CLOSED_PERMANENTLY
-
এই উদাহরণে, কোয়েরিটি নিউ ইয়র্ক সিটির এম্পায়ার স্টেট বিল্ডিংয়ের ১০০০ মিটার ব্যাসার্ধের মধ্যে OPERATIONAL এর business_status সহ সমস্ত ফুল বিক্রেতার গণনা প্রদান করে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000) AND business_status = "OPERATIONAL" AND 'florist' IN UNNEST(types)
কাজের সময় অনুসারে ফিল্টার করুন
এই উদাহরণে, শুক্রবারের শুভ ঘন্টা সহ একটি ভৌগোলিক এলাকার সমস্ত স্থানের গণনা ফেরত দিন:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places`, UNNEST(regular_opening_hours_happy_hour.friday) AS friday_hours WHERE '17:00:00' BETWEEN friday_hours.start_time AND friday_hours.end_time AND ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000);
অঞ্চল অনুসারে ফিল্টার করুন (ঠিকানার উপাদান)
আমাদের স্থান ডেটাসেটে ঠিকানা উপাদানগুলির একটি সেটও রয়েছে যা রাজনৈতিক সীমানার উপর ভিত্তি করে ফলাফল ফিল্টার করার জন্য কার্যকর। প্রতিটি ঠিকানা উপাদান তাদের টেক্সট কোড নাম (NYC-তে পোস্টাল কোডের জন্য 10002 ) বা সমতুল্য পোস্টাল কোড আইডির জন্য প্লেস আইডি ( ChIJm5NfgIBZwokR6jLqucW0ipg ) দ্বারা চিহ্নিত করা হয়।
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE '10002' IN UNNEST(postal_code_names) --- 'ChIJm5NfgIBZwokR6jLqucW0ipg' IN UNNEST(postal_code_ids) -- same filter as above using postal code ID
EV চার্জিং অনুসারে ফিল্টার করুন
এই উদাহরণে কমপক্ষে ৮টি ইভি চার্জার সহ স্থানের সংখ্যা গণনা করা হয়েছে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ev_charge_options.connector_count > 8;
এই উদাহরণে এমন স্থানের সংখ্যা গণনা করা হয়েছে যেখানে কমপক্ষে ১০টি টেসলা চার্জার দ্রুত চার্জিং সমর্থন করে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places`, UNNEST(ev_charge_options.connector_aggregation) as connectors WHERE connectors.type ='EV_CONNECTOR_TYPE_TESLA' AND connectors.max_charge_rate_kw >= 50 AND connectors.count >= 10
ফলাফল গ্রুপগুলি ফেরত দিন
এখন পর্যন্ত দেখানো কোয়েরিগুলি ফলাফলে একটি একক সারি ফেরত দেয় যাতে কোয়েরির সমষ্টি গণনা থাকে। গ্রুপিং মানদণ্ডের উপর ভিত্তি করে আপনি প্রতিক্রিয়ায় একাধিক সারি ফেরত দিতে GROUP BY অপারেটর ব্যবহার করতে পারেন।
উদাহরণস্বরূপ, নিম্নলিখিত কোয়েরিটি অনুসন্ধান এলাকার প্রতিটি স্থানের প্রাথমিক প্রকার অনুসারে গোষ্ঠীভুক্ত ফলাফল প্রদান করে:
SELECT WITH AGGREGATION_THRESHOLD primary_type, COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(ST_GEOGPOINT(-73.99992071622756, 40.71818785986936), point, 1000) GROUP BY primary_type
নিচের চিত্রটি এই কোয়েরির একটি উদাহরণ আউটপুট দেখায়:
এই উদাহরণে আপনি অবস্থানের একটি সারণী সংজ্ঞায়িত করবেন। প্রতিটি অবস্থানের জন্য আপনি কাছাকাছি রেস্তোরাঁর সংখ্যা গণনা করবেন, অর্থাৎ ১০০০ মিটারের মধ্যে থাকা রেস্তোরাঁগুলির সংখ্যা:
WITH my_locations AS ( SELECT 'Location 1' AS name, ST_GEOGPOINT(-74.00776440888504, 40.70932825380786) AS location UNION ALL SELECT 'Location 2' AS name, ST_GEOGPOINT(-73.98257192833559, 40.750738934863215) AS location UNION ALL SELECT 'Location 3' AS name, ST_GEOGPOINT(-73.94701794263223, 40.80792954838445) AS location ) SELECT WITH AGGREGATION_THRESHOLD l.name, COUNT(*) as count FROM `PROJECT_NAME.places_insights___us.places` JOIN my_locations l ON ST_DWITHIN(l.location, p.point, 1000) WHERE primary_type = "restaurant" AND business_status = "OPERATIONAL" GROUP BY l.name
নিচের চিত্রটি এই কোয়েরির একটি উদাহরণ আউটপুট দেখায়:
