सीमाओं के साथ कई प्रश्नों के "चौराहे" पाने के लिए MySQL क्वेरी
database select (7)
अपने मानदंड को फ़्लैट करें
आप अपने बहु-आयामी मानदंडों को एक स्तर के मानदंड में फ़्लैट कर सकते हैं
अब यह मानदंड एक प्रश्न में अनुसरण किए जा सकते हैं
(SELECT * FROM users WHERE gender = 'Male' AND region = 'North' LIMIT 40) UNION ALL
(SELECT * FROM users WHERE gender = 'Male' AND region = 'South' LIMIT 80) UNION ALL
(SELECT * FROM users WHERE gender = 'Male' AND region = 'East' LIMIT 120) UNION ALL
(SELECT * FROM users WHERE gender = 'Male' AND region = 'West' LIMIT 160) UNION ALL
(SELECT * FROM users WHERE gender = 'Female' AND region = 'North' LIMIT 60) UNION ALL
(SELECT * FROM users WHERE gender = 'Female' AND region = 'South' LIMIT 120) UNION ALL
(SELECT * FROM users WHERE gender = 'Female' AND region = 'East' LIMIT 180) UNION ALL
(SELECT * FROM users WHERE gender = 'Female' AND region = 'West' LIMIT 240)
मुसीबत
- यह हमेशा सही परिणाम नहीं देता है। उदाहरण के लिए, यदि 40 से कम उपयोगकर्ता हैं जिनके पुरुष और उत्तर से हैं, तो क्वेरी 1,000 से कम रिकॉर्ड लौटाएगी।
अपने मानदंड को समायोजित करें
मान लें कि 40 से कम उपयोगकर्ता हैं जिनके पुरुष और उत्तर से हैं। फिर, आपको "पुरुष" और "उत्तर" से गुम मात्रा को कवर करने के लिए अन्य मानदंड मात्रा समायोजित करने की आवश्यकता है। मुझे विश्वास है कि इसे बेयर एसक्यूएल के साथ करना संभव नहीं है। यह छद्म कोड है जो मुझे दिमाग में है। सरलीकरण के लिए, मुझे लगता है कि हम केवल पुरुष, महिला, उत्तर और दक्षिण के लिए पूछेंगे
conditions.add({ gender: 'Male', region: 'North', limit: 40 })
conditions.add({ gender: 'Male', region: 'South', limit: 80 })
conditions.add({ gender: 'Female', region: 'North', limit: 60 })
conditions.add({ gender: 'Female', region: 'South', limit: 120 })
foreach(conditions as condition) {
temp = getResultFromDatabaseByCondition(condition)
conditions.remove(condition)
// there is not enough result for this condition,
// increase other condition quantity
if (temp.length < condition.limit) {
adjust(...);
}
}
मान लें कि केवल 30 नॉर्थेनर पुरुष हैं। तो हमें +10 पुरुष, और +10 नॉर्थेनर समायोजित करने की आवश्यकता है।
To Adjust
---------------------------------------------------
Male +10
North +10
Remain Conditions
----------------------------------------------------
{ gender: 'Male', region: 'South', limit: 80 }
{ gender: 'Female', region: 'North', limit: 60 }
{ gender: 'Female', region: 'South', limit: 120 }
'पुरुष' + 'दक्षिण' पहली शर्त है जो 'पुरुष' समायोजन की स्थिति से मेल खाती है। इसे +10 तक बढ़ाएं, और इसे "स्थिति बने रहें" सूची से हटा दें। चूंकि, हम दक्षिण में वृद्धि करते हैं, हमें इसे दूसरी स्थिति में कम करने की आवश्यकता है। तो "समायोजित करने के लिए" सूची में "दक्षिण" स्थिति जोड़ें
To Adjust
---------------------------------------------------
South -10
North +10
Remain Conditions
----------------------------------------------------
{ gender: 'Female', region: 'North', limit: 60 }
{ gender: 'Female', region: 'South', limit: 120 }
Final Conditions
----------------------------------------------------
{ gender: 'Male', region: 'South', limit: 90 }
ऐसी स्थिति खोजें जो 'दक्षिण' से मेल खाती है और उसी प्रक्रिया को दोहराती है।
To Adjust
---------------------------------------------------
Female +10
North +10
Remain Conditions
----------------------------------------------------
{ gender: 'Female', region: 'North', limit: 60 }
Final Conditions
----------------------------------------------------
{ gender: 'Female', region: 'South', limit: 110 }
{ gender: 'Male', region: 'South', limit: 90 }
और अंत में
{ gender: 'Female', region: 'North', limit: 70 }
{ gender: 'Female', region: 'South', limit: 110 }
{ gender: 'Male', region: 'South', limit: 90 }
मैं अभी तक समायोजन के सटीक कार्यान्वयन के साथ नहीं आया है। यह अपेक्षा की तुलना में अधिक कठिन है। एक बार मैं इसे कार्यान्वित करने के तरीके को समझने के बाद अपडेट करूंगा।
मान लें कि मेरे पास निम्न फ़ील्ड के साथ एक एकल MySQL तालिका (उपयोगकर्ता) है:
userid
gender
region
age
ethnicity
income
मैं उपयोगकर्ता द्वारा दर्ज की गई संख्या के आधार पर कुल रिकॉर्ड की संख्या वापस करने में सक्षम होना चाहता हूं। इसके अलावा, वे अतिरिक्त मानदंड भी प्रदान करेंगे।
सबसे सरल उदाहरण में, वे 1,000 रिकॉर्ड मांग सकते हैं, जहां 600 रिकॉर्ड में लिंग = 'पुरुष' और 400 रिकॉर्ड होना चाहिए जहां लिंग = 'महिला'। यह करने के लिए काफी आसान है।
अब, एक कदम आगे जाओ। मान लें कि वे अब क्षेत्र निर्दिष्ट करना चाहते हैं:
GENDER
Male: 600 records
Female: 400 records
REGION
North: 100 records
South: 200 records
East: 300 records
West: 400 records
दोबारा, केवल 1000 रिकॉर्ड लौटाए जाएंगे, लेकिन अंत में, 600 पुरुष, 400 महिलाएं, 100 उत्तरी, 200 दक्षिणी, 300 पूर्वी और 400 पश्चिमी लोग होंगे।
मुझे पता है कि यह वैध वाक्यविन्यास नहीं है, लेकिन छद्म-mySQL कोड का उपयोग करके, यह उम्मीद करता है कि मैं क्या करने की कोशिश कर रहा हूं:
(SELECT * FROM users WHERE gender = 'Male' LIMIT 600
UNION
SELECT * FROM users WHERE gender = 'Female' LIMIT 400)
INTERSECT
(SELECT * FROM users WHERE region = 'North' LIMIT 100
UNION
SELECT * FROM users WHERE region = 'South' LIMIT 200
UNION
SELECT * FROM users WHERE region = 'East' LIMIT 300
UNION
SELECT * FROM users WHERE region = 'West' LIMIT 400)
ध्यान दें कि मैं एक बार की क्वेरी नहीं ढूंढ रहा हूं। रिकॉर्ड्स की कुल संख्या और प्रत्येक मानदंड के भीतर रिकॉर्ड्स की संख्या लगातार उपयोगकर्ता द्वारा इनपुट के आधार पर बदल जाएगी। इसलिए, मैं एक सामान्य समाधान के साथ आने की कोशिश कर रहा हूं जिसे दोबारा इस्तेमाल किया जा सकता है, न कि हार्ड-कोडेड समाधान।
चीजों को और अधिक जटिल बनाने के लिए, अब और मानदंड जोड़ें। प्रत्येक समूह के लिए अपने स्वयं के सेट संख्या के साथ आयु, जातीयता और आय भी हो सकती है, ऊपर दिए गए अतिरिक्त कोड:
INTERSECT
(SELECT * FROM users WHERE age >= 18 and age <= 24 LIMIT 300
UNION
SELECT * FROM users WHERE age >= 25 and age <= 36 LIMIT 200
UNION
SELECT * FROM users WHERE age >= 37 and age <= 54 LIMIT 200
UNION
SELECT * FROM users WHERE age >= 55 LIMIT 300)
INTERSECT
etc.
मुझे यकीन नहीं है कि एक प्रश्न में लिखना संभव है या यदि इसके लिए कई कथन और पुनरावृत्तियों की आवश्यकता है।
आपके अनुरोध के साथ समस्या यह है कि प्रस्तावित संख्याओं को प्राप्त करने के लिए बहुत से विकल्प हैं जिनका उपयोग किया जा सकता है:
Male Female Sum
-----------------------------
North: 100 0 100
South: 200 0 200
East: 300 0 300
West: 0 400 400
Sum: 600 400
-----------------------------
North: 99 1 100
South: 200 0 200
East: 300 0 300
West: 1 399 400
Sum: 600 400
-----------------------------
....
-----------------------------
North: 0 100 100
South: 200 0 200
East: 0 300 300
West: 400 0 400
Sum: 600 400
बस उत्तर, पूर्व और पश्चिम (दक्षिण हमेशा पुरुष: 200) के संयोजन से आपको प्रस्तावित संख्याओं को प्राप्त करने के लिए 400 संभावनाएं मिलेंगी। और यह तब भी जटिल हो जाता है जब आपके पास प्रत्येक " वर्ग " (पुरुष / उत्तर = " वर्ग ") के लिए केवल सीमित मात्रा में रिकॉर्ड होते हैं।
उपर्युक्त तालिका में प्रत्येक सेल के लिए आपको MIN(COUNT(gender), COUNT(location)) रिकॉर्ड्स की आवश्यकता हो सकती है (यदि यह समकक्ष शून्य होगा)।
यह इस पर निर्भर है:
Male Female
---------------------
North: 100 100
South: 200 200
East: 300 300
West: 400 400
इसलिए आपको प्रत्येक लिंग / स्थान जोड़ी AVAILABLE(gender, location) उपलब्ध रिकॉर्ड गिनने की आवश्यकता है।
विशेष फिट ढूंढना सेमेमाजिक वर्गों के करीब होना प्रतीत होता है [1] [2] ।
और इस [3] [4] बारे में math.stackexchange.com पर कई प्रश्न हैं।
मैंने इनका निर्माण करने के तरीके पर कुछ पेपर पढ़ना समाप्त कर दिया है और मुझे संदेह है कि इसे एक चयन के साथ करना संभव है।
यदि आपके पास पर्याप्त रिकॉर्ड हैं और इस तरह की स्थिति में खत्म नहीं होंगे:
Male Female
---------------------
North: 100 0
South: 200 200
East: 300 0
West: 200 200
मैं गड़बड़ी वाले स्थानों के साथ जाऊंगा और प्रत्येक चरण में पुरुषों / महिलाओं की आनुपातिक संख्या जोड़ूंगा:
- एम: 100 (16%); एफ: 0 (0%)
- एम: 100 (16%); एफ: 200 (50%)
- एम: 400 (66%); एफ: 200 (50%)
- एम: 600 (100%); एफ: 400 (100%)
लेकिन यह आपको केवल अनुमानित परिणाम देगा और उनको सत्यापित करने के बाद आप कुछ बार कठिन परिणाम फिर से शुरू करना चाहते हैं और प्रत्येक श्रेणी में गणना को " पर्याप्त " होने के लिए समायोजित कर सकते हैं।
इसे दो चरणों में हल किया जा सकता है। मैं वर्णन करूंगा कि उदाहरण के लिए लिंग और क्षेत्र आयाम हैं। तो मैं अधिक सामान्य मामले का वर्णन करूंगा। पहले चरण में हम 8 चर के समीकरणों की एक प्रणाली को हल करते हैं, फिर हम चरण एक में पाए गए समाधानों द्वारा सीमित 8 चुनिंदा बयानों का विघटन संघ लेते हैं। ध्यान दें कि किसी भी पंक्ति के लिए केवल 8 संभावनाएं हैं। वे नर या मादा हो सकते हैं और फिर यह क्षेत्र उत्तर, दक्षिण, पूर्व या पश्चिम में से एक है। अब छोडो,
X1 equal the number of rows that are male and from the north,
X2 equal the number of rows that are male and from the south,
X3 equal the number of rows that are male and from the east,
X4 equal then number that are male and from the west
X5 equal the number of rows that are female and from the north,
X6 equal the number of rows that are female and from the south,
X7 equal the number of rows that are female and from the east,
X8 equal then number that are female and from the west
समीकरण हैं:
X1+X2+X3+X4=600
X5+X6+X7+X8=400
X1+X5=100
X2+X6=200
X3+X7=300
X4+X8=400
अब एक्स 1, एक्स 2, ... एक्स 8 के लिए हल करें। कई समाधान हैं (मैं एक पल में हल करने का वर्णन करूंगा) यहां एक समाधान है:
X1=60, X2=120, X3=180,X4=240,X5=40,X6=80,X7=120,X8=160.
अब हम 8 चयनों के एक साधारण संघ द्वारा परिणाम प्राप्त कर सकते हैं:
(select * from user where gender='m' and region="north" limit 60)
union distinct(select * from user where gender='m' and region='south' limit 120)
union distinct(select * from user where gender='m' and region='east' limit 180)
union distinct(select * from user where gender='m' and region='west' limit 240)
union distinct(select * from user where gender='f' and region='north' limit 40)
union distinct(select * from user where gender='f' and region='south' limit 80)
union distinct(select * from user where gender='f' and region='east' limit 120)
union distinct(select * from user where gender='f' and region='west' limit 160);
ध्यान दें कि यदि डेटा बेस में 60 पंक्तियां नहीं हैं तो ऊपर दिए गए पहले चयन को पूरा करें, तो दिया गया विशेष समाधान काम नहीं करेगा। तो हमें अन्य बाधाओं को जोड़ना है, एलटी:
0<X1 <= (select count(*) from user where from user where gender='m' and region="north")
0<X2 <= (select count(*) from user where gender='m' and region='south')
0<X3 <= (select count(*) from user where gender='m' and region='east' )
0<X4 <= (select count(*) from user where gender='m' and region='west')
0<X5 <= (select count(*) from user where gender='f' and region='north' )
0<X6 <= (select count(*) from user where gender='f' and region='south')
0<X7 <= (select count(*) from user where gender='f' and region='east' )
0<X8 <= (select count(*) from user where gender='f' and region='west');
अब इस मामले के लिए किसी भी विभाजन की अनुमति देने के लिए सामान्यीकृत करते हैं। समीकरण ई हैं:
X1+X2+X3+X4=n1
X5+X6+X7+X8=n2
X1+X5=m1
X2+X6=m2
X3+X7=m3
X4+X8=m4
एन संख्या एन 1, एन 2, एम 1, एम 2, एम 3, एम 4 दिए गए हैं और एन 1 + एन 2 = (एम 1 + एम 2 + एम 3 + एम 4) को संतुष्ट करते हैं। इसलिए हमने उपरोक्त समीकरण एलटी और ई को हल करने के लिए समस्या को कम कर दिया है। यह सिर्फ एक रैखिक प्रोग्रामिंग समस्या है और सरल विधि या अन्य विधियों का उपयोग करके हल किया जा सकता है। एक और संभावना यह है कि इसे रैखिक डायफोंटाइन समीकरणों की एक प्रणाली के रूप में देखना और समाधान खोजने के लिए इसके तरीकों का उपयोग करना है। किसी भी मामले में मैंने उपरोक्त समीकरणों के समाधान को खोजने के लिए समस्या को कम कर दिया है। (यह देखते हुए कि समीकरण एक विशेष रूप के होते हैं, फिर सरल विधि का उपयोग करके या रैखिक डायफोंटाइन समीकरणों की प्रणाली को हल करने का एक तेज़ तरीका हो सकता है)। जब हम ज़ी के लिए हल करते हैं तो अंतिम समाधान होता है:
(select * from user where gender='m' and region="north" limit :X1)
union distinct(select * from user where gender='m' and region='south' limit :X2)
union distinct(select * from user where gender='m' and region='east' limit :X3)
union distinct(select * from user where gender='m' and region='west' limit :X4)
union distinct(select * from user where gender='f' and region='north' limit :X5)
union distinct(select * from user where gender='f' and region='south' limit :X6)
union distinct(select * from user where gender='f' and region='east' limit :X7)
union distinct(select * from user where gender='f' and region='west' limit :X8);
आइए डी के रूप में एन संभावनाओं के साथ आयाम डी को इंगित करें: एन। मान लें कि आपके पास डी 1: एन 1, डी 2: एन 2, ... डीएम: एनएम आयाम हैं। एन 1 * एन 2 * ... एनएम चर उत्पन्न करेगा। उत्पन्न समीकरणों की संख्या एन 1 + एन 2 + ... एनएम है। इसके बजाय सामान्य विधि को परिभाषित करने के लिए 3 आयामों, 4 आयामों और 2 आयामों का एक और मामला लें; आइए डी 1 के लिए संभावित मानों को डी 11, डी 12, डी 13, डी 2 डी 21, डी 22, डी 23, डी 24, और डी 3 मान डी 31, डी 32 हैं। हमारे पास 24 चर होंगे, और समीकरण हैं:
X1 + X2 + ...X8=n11
X9 + X10 + ..X16=n12
X17+X18 + ...X24=n13
X1+X2+X9+x10+x17+x18=n21
X3+X4+X11+x12+x19+x20=n22
X5+X6+X13+x14+x21+x22=n23
X7+X8+X15+x116+x23+x24=n24
X1+X3+X5+...X23=n31
X2+X4+......X24=n32
कहा पे
X1 equals number with D1=d11 and D2=d21 and D3=d31
X2 equals number with D1=d11 and D2=d21 and D3 = d31
....
X24 equals number with D1=D13 and D2=d24, and D3=d32.
कम बाधाओं को जोड़ें। फिर X1, X2, ... X24 के लिए हल करें। 24 चयन कथन बनाएं और विवाद संघ लें। हम किसी भी आयाम के लिए इसी तरह हल कर सकते हैं।
तो संक्षेप में: दिए गए आयाम डी 1: एन 1, डी 2: एन 2, ... डीएम: एनएम हम एन 1 * एन 2 * ... एनएम चर के लिए ऊपर वर्णित संबंधित रैखिक प्रोग्रामिंग समस्या को हल कर सकते हैं और फिर विवाद को लेकर समाधान उत्पन्न कर सकते हैं एन 1 * एन 2 * पर संघ ... एनएम चयन कथन। तो हाँ, हम चुनिंदा वक्तव्यों द्वारा समाधान उत्पन्न कर सकते हैं लेकिन पहले हमें समीकरणों को हल करना होगा और एन 1 * एन 2 * ... एनएम चर के लिए गणना प्राप्त करके सीमा निर्धारित करना होगा।
भले ही बक्षीस खत्म हो गया है, मैं उन लोगों के लिए थोड़ा और जोड़ने जा रहा हूं जिनकी आप रुचि रखते हैं। मैं यहां दावा करता हूं कि अगर मैंने समाधान किया है तो मैंने इसे पूरी तरह से कैसे हल किया है।
मेरे दृष्टिकोण को स्पष्ट करने के लिए। 3 आयामों के मामले में, मान लें कि हम उम्र को 3 संभावनाओं में से एक में विभाजित करते हैं। फिर सवाल के रूप में लिंग और क्षेत्र का अच्छी तरह से उपयोग करें। प्रत्येक उपयोगकर्ता के लिए उन श्रेणियों में जहां वे गिरते हैं, उनके लिए 24 अलग-अलग संभावनाएं हैं। अंतिम परिणाम में Xi उन संभावनाओं में से प्रत्येक की संख्या होने दें। मुझे एक मैट्रिक्स लिखने दें जहां प्रत्येक पंक्ति प्रत्येक संभावना में से एक का प्रतिनिधित्व करती है। प्रत्येक उपयोगकर्ता अधिकतम 1 से एम या एफ, 1 उत्तर, दक्षिण, पूर्व या पश्चिम में 1 और आयु वर्ग में योगदान देगा। और उपयोगकर्ता के लिए केवल 24 संभावनाएं हैं। चलिए एक मैट्रिक्स दिखाते हैं: (एबीसी) 3 आयु, (एनएसयू) क्षेत्रों और (एमएफ) नर या मादा: आयु कम या उसके बराबर है, बी 11 से 30 वर्ष की आयु है और सी 31 और 50 के बीच है ।
abc nsew mf
X1 100 1000 10
X2 100 1000 01
X3 100 0100 10
X4 100 0100 01
X5 100 0010 10
X6 100 0010 01
X7 100 0001 10
X8 100 0001 01
X9 010 1000 10
X10 010 1000 01
X11 010 0100 10
X12 010 0100 01
X13 010 0010 10
X14 010 0010 01
X15 010 0001 10
X16 010 0001 01
X17 001 1000 10
X18 001 1000 01
X19 001 0100 10
X20 001 0100 01
X21 001 0010 10
X22 001 0010 01
X23 001 0001 10
X24 001 0001 01
प्रत्येक पंक्ति उस उपयोगकर्ता का प्रतिनिधित्व करती है जहां कॉलम में 1 होता है यदि यह परिणाम में योगदान देता है। उदाहरण के लिए, पहली पंक्ति 1 के लिए 1, एन के लिए 1 और एम के लिए 1 दिखाती है। जिसका अर्थ है कि उपयोगकर्ता की उम्र कम या 10 के बराबर है, उत्तर से है और एक पुरुष है। Xi दर्शाता है कि अंतिम परिणाम में उस तरह की पंक्ति कितनी पंक्ति है। तो मान लें कि एक्स 1 10 है जिसका मतलब है कि हम कहते हैं कि अंतिम परिणाम में 10 परिणाम हैं जो उत्तर से हैं, पुरुष हैं और कम या बराबर हैं 10. ठीक है तो अब हमें चीजों को जोड़ना है। ध्यान दें कि पहले 8 X1+X2+X3+X4+X5+X6+X7+X8 सभी पंक्तियां हैं जिनकी उम्र कम या 10 के बराबर है। उन्हें उस श्रेणी के लिए जो कुछ भी चुना गया है, उसे जोड़ना चाहिए। इसी प्रकार 8 के अगले 2 सेट के लिए।
तो अब तक हम समीकरण प्राप्त करते हैं: (ना 10 से कम उम्र के साथ संख्या है, एनबी उम्र 10 से 20 के बीच, एनसी जिसकी उम्र 50 वर्ष से कम है
X1+X2+X3+X4+X5+X6+X7+X8 = na
X9+X10+X11 + .... X16 = nb
X17+X18+X19+... X24=nc
वे उम्र विभाजित हैं। अब क्षेत्र विभाजन को देखते हैं। बस "एन" कॉलम में चर जोड़ें,
X1+X2+X9+X10+X17+X18 = nn
X3+X4+X11+X12+X19+20=ns
...
आदि। क्या आप देखते हैं कि कॉलम को देखकर मैं उन समीकरणों को कैसे प्राप्त कर रहा हूं? ईडब्ल्यू और एमएफ के लिए जारी रखें। कुल में 3 + 4 + 2 समीकरण देना। तो मैंने यहां क्या किया है काफी सरल है। मैंने तर्क दिया है कि आपके द्वारा चुनी गई कोई भी पंक्ति 3 आयामों में से प्रत्येक में योगदान देती है और केवल 24 संभावनाएं होती हैं। फिर Xi को प्रत्येक संभावना के लिए संख्या दें और आपको समीकरणों को हल करने की आवश्यकता है। ऐसा लगता है कि आप जिस भी विधि के साथ आते हैं वह उन समीकरणों का समाधान होना चाहिए। दूसरे शब्दों में मैंने बस उन समीकरणों को हल करने के मामले में समस्या को सुधार दिया।
अब हम एक पूर्णांक समाधान चाहते हैं क्योंकि हमारे पास एक आंशिक पंक्ति नहीं हो सकती है। ध्यान दें ये सभी रैखिक समीकरण हैं। लेकिन हम एक पूर्णांक समाधान चाहते हैं। यहां एक पेपर का एक लिंक है जो वर्णन करता है कि इन्हें कैसे हल किया जाए: https://www.math.uwaterloo.ca/~wgilbert/Research/GilbertPathria.pdf
एसक्यूएल में बिजनेस लॉजिक बनाना कभी अच्छा विचार नहीं है क्योंकि इससे मामूली बदलावों को अवशोषित करने की क्षमता में बाधा आती है।
मेरा सुझाव यह है कि इसे ओआरएम में करना होगा और एसक्यूएल से व्यापार तर्क को समेकित रखना होगा।
उदाहरण के लिए यदि आप Django का उपयोग कर रहे थे:
आपका मॉडल इस तरह दिखेगा:
class User(models.Model):
GENDER_CHOICES = (
('M', 'Male'),
('F','Female')
)
gender = models.CharField(max_length=1, choices=GENDER_CHOICES)
REGION_CHOICES = (
('E', 'East'),
('W','West'),
('N','North'),
('S','South')
)
region = models.CharField(max_length=1, choices=REGION_CHOICES)
age = models.IntegerField()
ETHNICITY_CHOICES = (
.......
)
ethnicity = models.CharField(max_length=1, choices=ETHNICITY_CHOICES)
income = models.FloatField()
और आपका क्वेरी फ़ंक्शन ऐसा कुछ हो सकता है:
# gender_limits is a dict like {'M':400, 'F':600}
# region_limits is a dict like {'N':100, 'E':200, 'W':300, 'S':400}
def get_users_by_gender_and_region(gender_limits,region_limits):
for gender in gender_limits:
gender_queryset = gender_queryset | User.objects.filter(gender=gender)[:gender_limits[gender]]
for region in region_limits:
region_queryset = region_queryset | User.objects.filter(region=region)[:region_limits[region]]
return gender_queryset & region_queryset
क्वेरी फ़ंक्शन को उन सभी प्रश्नों के ज्ञान के साथ आगे समझाया जा सकता है जिन्हें आप समर्थन देने की योजना बना रहे हैं, लेकिन इसे एक उदाहरण के रूप में कार्य करना चाहिए।
यदि आप एक अलग ओआरएम का उपयोग कर रहे हैं, तो उसी विचार का भी अनुवाद किया जा सकता है क्योंकि किसी भी अच्छे ओआरएम में संघ और चौराहे का अबाउटक्शन होगा।
मैं डेटाबेस के वितरण का नक्शा तैयार करता हूं और नमूना तर्क लागू करने के लिए इसका उपयोग करता हूं। बोनस में उपयोगकर्ता को त्वरित जनसांख्यिकीय प्रतिक्रिया जोड़ने और सर्वर पर कोई अतिरिक्त बोझ जोड़ने की संभावना नहीं है। कॉन साइड पर, आपको डेटाबेस और मानचित्र को सिंक में रखने के लिए एक तंत्र को लागू करने की आवश्यकता होगी।
यह JSON का उपयोग करके ऐसा दिखाई दे सकता है:
{"gender":{
"Male":{
"amount":35600,
"region":{
"North":{
"amount":25000,
"age":{
"18":{
"amount":2400,
"ethnicity":{
...
"income":{
...
}
},
"income":{
...
"ethnicity":{
...
}
}
},
"19":{
...
},
...
"120":{
...
}
},
"ethnicity":{
...
},
"income":{
...
}
},
"South":{
...
},
...
}
"age":{
...
}
"ethnicity":{
...
},
"income":{
...
}
},
"Female":{
...
}
},
"region":{
...
},
"age":{
...
},
"ethnicity":{
...
},
"income":{
...
}}
तो उपयोगकर्ता चुनता है
total 1000
600 Male
400 Female
100 North
200 South
300 East
400 West
300 <20 years old
300 21-29 years old
400 >=30 years old
एक रैखिक वितरण की गणना करें:
male-north-u20: 1000*0.6*0.1*0.3=18
male-north-21to29: 18
male-north-o29: 24 (keep a track of rounding errors)
etc
तो हम मानचित्र की जांच करेंगे:
tmp.male.north.u20=getSumUnder(JSON.gender.Male.region.North.age,20) // == 10
tmp.male.north.f21to29=getSumBetween(JSON.gender.Male.region.North.age,21,29) // == 29
tmp.male.north.o29=getSumOver(JSON.gender.Male.region.north.age,29) // == 200
etc
रैखिक वितरण को पूरा करने वाले सभी चीजों को ठीक करें और अधिशेष का ट्रैक रखें। अगर कुछ (जैसे male.north.u20) पहले माता-पिता में समायोजित होता है (यह सुनिश्चित करने के लिए कि पुरुष। नॉर्थ मानदंडों को पूरा करता है), तो आपको यू 20 के लिए 8 गुम हो जाता है और f21to29 के लिए 8 का उपयोग किया जाता है। पहले चलाने के बाद अन्य क्षेत्रों में प्रत्येक लापता मानदंड समायोजित करें। तो tmp.male.south.u20+=8;tmp.male.south.f21to29-=8; ।
यह सही पाने के लिए यह बहुत कठिन है।
अंत में आपके पास सही वितरण है जिसका उपयोग एक छोटी SQL क्वेरी बनाने के लिए किया जा सकता है।
I expect you'd want to generate a bunch of queries based on the required filters.
I'll explain a possible approach, with a full code sample - but note the caveats later on.
I'll also address the issue where you can't fulfil the requested sample from a proportional distribution, but you can from an adjusted distribution - and explain how to do that adjustment
The basic algorithm goes like this:
Start with a set of filters {F1, F2, ... Fn} , each which has a group of values, and percentages which should be distributed amongst those values. For example F1 might be gender, with 2 values (F1V1 = Male: 60%, F1V2 = Female: 40%) You'll also want the total sample size required (call this X ) From this starting point you can then combine all the filters items from each filter to get a single set all of the combined filter items, and the quantities required for each. The code should be able to handle any number of filters, with any number of values (either exact values, or ranges)
EG: suppose 2 filters, F1: gender, {F1V1 = Male: 60%, F1V2 = Female: 40%}, F2: region, {F2V1 = North: 50%, F2V2 = South: 50%} and a total sample required of X = 10 people.
In this sample we'd like 6 of them to be male, 4 of them to be female, 5 to be from the north, and 5 to be from the south.
Then we do
- Create an sql stub for each value in F1 - with an associated fraction of the initial percentage (ie
-
WHERE gender = 'Male': 0.6, -
WHERE gender = 'Female': 0.4 )
-
- For each item in F2 - create a new sql stub from every item from the step above - with the filter now being both the F1 Value & the F2 Value, and the associated fraction being the product of the 2 fractions. So we now have 2 x 2 = 4 items of
-
WHERE gender = 'Male' AND region = 'North': 0.6 * 0.5 = 0.3, -
WHERE gender = 'Female' AND region = 'North': 0.4 * 0.5 = 0.2, -
WHERE gender = 'Male' AND region = 'South': 0.6*0.5 = 0.3, -
WHERE gender = 'Female' AND region = 'South': 0.4*0.5 = 0.2
-
- Repeat step 2 above for every additional Filter F3 to Fn. (in our example there were only 2 filters, so we are already done)
- Calculate the limit for each SQL stub as being [fraction associated with stub] * X = total required sample size (so for our example thats 0.3 * 10 = 3 for Male/North, 0.2 * 10 = 2 for Female/North etc)
- Finally for every sql stub - turn it into a complete SQL statement , and add the limit
Code Sample
I'll provide C# code for this, but it should be easy enough to translate this to other languages. It would be pretty tricky to attempt this in pure dynamic SQL
Note this is untested - and probably full of errors - but its an idea of the approach you could take.
I've defined a public method and a public class - which would be the entry point.
// This is an example of a public class you could use to hold one of your filters
// For example - if you wanted 60% male / 40% female, you could have an item with
// item1 = {Fraction: 0.6, ValueExact: 'Male', RangeStart: null, RangeEnd: null}
// & item2 = {Fraction: 0.4, ValueExact: 'Female', RangeStart: null, RangeEnd: null}
public class FilterItem{
public decimal Fraction {get; set;}
public string ValueExact {get; set;}
public int? RangeStart {get; set;}
public int? RangeEnd {get; set;}
}
// This is an example of a public method you could call to build your SQL
// - passing in a generic list of desired filter
// for example the dictionary entry for the above filter would be
// {Key: "gender", Value: new List<FilterItem>(){item1, item2}}
public string BuildSQL(Dictionary<string, List<FilterItem>> filters, int TotalItems)
{
// we want to build up a list of SQL stubs that can be unioned together.
var sqlStubItems = new List<SqlItem>();
foreach(var entry in filters)
{
AddFilter(entry.Key, entry.Value, sqlStubItems);
}
// ok - now just combine all of the sql stubs into one big union.
var result = ""; // Id use a stringbuilder for this normally,
// but this is probably more cross-language readable.
int limitSum = 0;
for(int i = 0; i < sqlStubItems.Count; i++) // string.Join() would be more succinct!
{
var item = sqlStubItems[i];
if (i > 0)
{
result += " UNION ";
}
int limit = (int)Math.Round(TotalItems * item.Fraction, 0);
limitSum+= limit;
if (i == sqlStubItems.Count - 1 && limitSum != TotalItems)
{
//may need to adjust one of the rounded items to account
//for rounding errors making a total that is not the
//originally required total limit.
limit += (TotalItems - limitSum);
}
result += item.Sql + " LIMIT "
+ Convert.ToString(limit);
}
return result;
}
// This method expands the number of SQL stubs for every filter that has been added.
// each existing filter is split by the number of items in the newly added filter.
private void AddFilter(string filterType,
List<FilterItem> filterValues,
List<SqlItem> SqlItems)
{
var newItems = new List<SqlItem>();
foreach(var filterItem in filterValues)
{
string filterAddon;
if (filterItem.RangeStart.HasValue && filterItem.RangeEnd.HasValue){
filterAddon = filterType + " >= " + filterItem.RangeStart.ToString()
+ " AND " + filterType + " <= " + filterItem.RangeEnd.ToString();
} else {
filterAddon = filterType + " = '"
+ filterItem.ValueExact.Replace("'","''") + "'";
//beware of SQL injection. (hence the .Replace() above)
}
if(SqlItems.Count() == 0)
{
newItems.Add(new SqlItem(){Sql = "Select * FROM users WHERE "
+ filterAddon, Fraction = filterItem.Fraction});
} else {
foreach(var existingItem in SqlItems)
{
newItems.Add(new SqlItem()
{
Sql = existingItem + " AND " + filterAddon,
Fraction = existingItem.Fraction * filterItem.Fraction
});
}
}
}
SqlItems.Clear();
SqlItems.AddRange(newItems);
}
// this class is for part-built SQL strings, with the fraction
private class SqlItem{
public string Sql { get; set;}
public decimal Fraction{get; set;}
}
Notes (as per comment by Sign)
- Rounding errors may mean you don't get exactly the 600 / 400 split you were aiming for when applying a large number of filters - but should be close.
- If your dataset is not very diverse then it may not be possible to always generate the required split. This method will require an even distribution amongst the filters (so if you were doing a total of 10 people, 6 male, 4 female , 5 from the north, 5 from the south it would require 3 males from the north, 3 males from the south, 2 females from the north and 2 females from the south.)
- The people are not going to be retrieved at random - just whatever the default sort is. You would need to add something like ORDER BY RAND() (but not that as its VERY inefficient) to get a random selection.
- Beware of SQL injection. Sanitise all user input, replacing single quote
'chars.
Badly distributed sample problem
How do you address the problem of there being insufficient items in one of our buckets to create our sample as per a representative split (that the above algorithm gives)? Or what if your numbers are not integers?
Well I won't go so far as to provide code, but I will describe a possible approach. You'd need to alter the code above quite a bit, because a flat list of sql stubs isn't going to cut it anymore. Instead you'd need to build a n-dimensional matrix of SQL stubs (adding a dimension for every filter F1 - n) After step 4 above has been completed (where we have our desired, but not necessarily possible numbers for each SQL stub item), what I'd expect to do is
- generate SQL to select counts for all the combined sql WHERE stubs.
- Then you'd iterate the collection - and if you hit an item where the requested limit is higher than the count (or not an integer),
- adjust the requested limit down to the count (or nearest integer).
- Then pick another item on each of the axis that is at least the above adjustment lower that its max count, and adjust it up by the same. If its not possible to find qualifying items then your requested split is not possible.
- Then adjust all the intersecting items for the upward adjusted items down again
- Repeat the step above for intersects between the intersecting points for every additional dimension to n (but toggle the adjustment between negative and positive each time)
So suppose continuing our previous example - our representative split is:
Male/North = 3, Female/North = 2, Male/South = 3, Female/South = 2, but there are only 2 Males in the north (but theres loads of people in the other groups we could pick)
- We adjust Male/North down to 2 (-1)
- We adjust Female/North to 3 (+1) and Male/South to 4 (+1)
- We adjust the Intersecting Female/South to 1 (-1). देखा! (there are no additional dimensions as we only had 2 criteria/dimensions)
This illustration may be helpful when adjusting intersecting items in higher dimensions (only showing up to 4 dimensions, but should help to picture what needs to be done! Each point represents one of our SQL stub items in the n-dimensional matrix (and has an associated limit number) A line represents a common criteria value (such as gender = male). The objective is that the total along any line should remain the same after adjustments have finished! We start with the red point, and continue for each additional dimension... In the example above we would only be looking at 2 dimensions - a square formed from the red point, the 2 orange points above and to the right of it, and the 1 green point to the NE to complete the square.
Well, I think the question is about randomly getting the records and not in the proportion of 60/40 for all regions. I have done for Region and Gender. It can be generalized to other fields like age, income and ethnicity in the same way.
Declare @Mlimit bigint
Declare @Flimit bigint
Declare @Northlimit bigint
Declare @Southlimit bigint
Declare @Eastlimit bigint
Declare @Westlimit bigint
Set @Mlimit= 600
Set @Flimit=400
Set @Northlimit= 100
Set @Southlimit=200
Set @Eastlimit=300
Set @Westlimit=400
CREATE TABLE #Users(
[UserId] [int] NOT NULL,
[gender] [varchar](10) NULL,
[region] [varchar](10) NULL,
[age] [int] NULL,
[ethnicity] [varchar](50) NULL,
[income] [bigint] NULL
)
Declare @MnorthCnt bigint
Declare @MsouthCnt bigint
Declare @MeastCnt bigint
Declare @MwestCnt bigint
Declare @FnorthCnt bigint
Declare @FsouthCnt bigint
Declare @FeastCnt bigint
Declare @FwestCnt bigint
Select @MnorthCnt=COUNT(*) from users where gender='male' and region='north'
Select @FnorthCnt=COUNT(*) from users where gender='female' and region='north'
Select @MsouthCnt=COUNT(*) from users where gender='male' and region='south'
Select @FsouthCnt=COUNT(*) from users where gender='female' and region='south'
Select @MeastCnt=COUNT(*) from users where gender='male' and region='east'
Select @FeastCnt=COUNT(*) from users where gender='female' and region='east'
Select @MwestCnt=COUNT(*) from users where gender='male' and region='west'
Select @FwestCnt=COUNT(*) from users where gender='female' and region='west'
If (@[email protected][email protected])
begin
Insert into #Users select * from Users where region='north'
set @Northlimit=0
set @[email protected]
set @[email protected]
set @MnorthCnt=0
set @FnorthCnt=0
end
If (@[email protected][email protected])
begin
Insert into #Users select * from Users where region='South'
set @Southlimit=0
set @[email protected]
set @[email protected]
set @MsouthCnt=0
set @FsouthCnt=0
end
If (@[email protected][email protected])
begin
Insert into #Users select * from Users where region='East'
set @Eastlimit=0
set @[email protected]
set @[email protected]
set @MeastCnt=0
set @FeastCnt=0
end
If (@[email protected][email protected])
begin
Insert into #Users select * from Users where region='West'
set @Westlimit=0
set @[email protected]
set @[email protected]
set @MwestCnt=0
set @FwestCnt=0
end
If @MnorthCnt<@Northlimit
Begin
insert into #Users select top (@[email protected]) * from Users where gender='female' and region='north'
and userid not in (select userid from #users)
set @Flimit-=(@[email protected])
set @FNorthCnt-=(@[email protected])
set @Northlimit-=(@[email protected])
End
If @FnorthCnt<@Northlimit
Begin
insert into #Users select top (@[email protected]) * from Users where gender='male' and region='north'
and userid not in (select userid from #users)
set @Mlimit-=(@[email protected])
set @MNorthCnt-=(@[email protected])
set @Northlimit-=(@[email protected])
End
if @MsouthCnt<@southlimit
Begin
insert into #Users select top (@[email protected]) * from Users where gender='female' and region='south'
and userid not in (select userid from #users)
set @Flimit-=(@[email protected])
set @FSouthCnt-=(@[email protected])
set @southlimit-=(@[email protected])
End
if @FsouthCnt<@southlimit
Begin
insert into #Users select top (@[email protected]) * from Users where gender='male' and region='south'
and userid not in (select userid from #users)
set @Mlimit-=(@[email protected])
set @MSouthCnt-=(@[email protected])
set @southlimit-=(@[email protected])
End
if @MeastCnt<@eastlimit
Begin
insert into #Users select top (@[email protected]) * from Users where gender='female' and region='east'
and userid not in (select userid from #users)
set @Flimit-=(@[email protected])
set @FEastCnt-=(@[email protected])
set @eastlimit-=(@[email protected])
End
if @FeastCnt<@eastlimit
Begin
insert into #Users select top (@[email protected]) * from Users where gender='male' and region='east'
and userid not in (select userid from #users)
set @Mlimit-=(@[email protected])
set @MEastCnt-=(@[email protected])
set @eastlimit-=(@[email protected])
End
if @MwestCnt<@westlimit
Begin
insert into #Users select top (@[email protected]) * from Users where gender='female' and region='west'
and userid not in (select userid from #users)
set @Flimit-=(@[email protected])
set @FWestCnt-=(@[email protected])
set @westlimit-=(@[email protected])
End
if @FwestCnt<@westlimit
Begin
insert into #Users select top (@[email protected]) * from Users where gender='male' and region='west'
and userid not in (select userid from #users)
set @Mlimit-=(@[email protected])
set @MWestCnt-=(@[email protected])
set @westlimit-=(@[email protected])
End
IF (@MnorthCnt>[email protected] and @FnorthCnt>[email protected] and @MsouthCnt>[email protected] and @FsouthCnt>[email protected] and @MeastCnt>[email protected] and @FeastCnt>[email protected] and @MwestCnt>[email protected] and @FwestCnt>[email protected] and not(@Mlimit=0 and @Flimit=0))
Begin
---Create Cursor
DECLARE UC CURSOR FAST_forward
FOR
SELECT *
FROM Users
where userid not in (select userid from #users)
Declare @UserId [int] ,
@gender [varchar](10) ,
@region [varchar](10) ,
@age [int] ,
@ethnicity [varchar](50) ,
@income [bigint]
OPEN UC
FETCH NEXT FROM UC
INTO @UserId ,@gender, @region, @age, @ethnicity, @income
WHILE @@FETCH_STATUS = 0 and not (@Mlimit=0 and @Flimit=0)
BEGIN
If @gender='male' and @region='north' and @Northlimit>0 AND @Mlimit>0
begin
insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income)
set @Mlimit-=1
set @MNorthCnt-=1
set @Northlimit-=1
end
If @gender='male' and @region='south' and @southlimit>0 AND @Mlimit>0
begin
insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income)
set @Mlimit-=1
set @MsouthCnt-=1
set @Southlimit-=1
end
If @gender='male' and @region='east' and @eastlimit>0 AND @Mlimit>0
begin
insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income)
set @Mlimit-=1
set @MeastCnt-=1
set @eastlimit-=1
end
If @gender='male' and @region='west' and @westlimit>0 AND @Mlimit>0
begin
insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income)
set @Mlimit-=1
set @MwestCnt-=1
set @westlimit-=1
end
If @gender='female' and @region='north' and @Northlimit>0 AND @flimit>0
begin
insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income)
set @Flimit-=1
set @FNorthCnt-=1
set @Northlimit-=1
end
If @gender='female' and @region='south' and @southlimit>0 AND @flimit>0
begin
insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income)
set @Flimit-=1
set @FsouthCnt-=1
set @Southlimit-=1
end
If @gender='female' and @region='east' and @eastlimit>0 AND @flimit>0
begin
insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income)
set @flimit-=1
set @feastCnt-=1
set @eastlimit-=1
end
If @gender='female' and @region='west' and @westlimit>0 AND @flimit>0
begin
insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income)
set @flimit-=1
set @fwestCnt-=1
set @westlimit-=1
end
FETCH NEXT FROM UC
INTO @UserId ,@gender, @region, @age, @ethnicity, @income
END
CLOSE UC
DEALLOCATE UC
end
Select * from #Users
SELECT GENDER, REGION, COUNT(*) AS COUNT FROM #USERS
GROUP BY GENDER, REGION
DROP TABLE #Users
