सीमाओं के साथ कई प्रश्नों के "चौराहे" पाने के लिए 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 

मैं गड़बड़ी वाले स्थानों के साथ जाऊंगा और प्रत्येक चरण में पुरुषों / महिलाओं की आनुपातिक संख्या जोड़ूंगा:

  1. एम: 100 (16%); एफ: 0 (0%)
  2. एम: 100 (16%); एफ: 200 (50%)
  3. एम: 400 (66%); एफ: 200 (50%)
  4. एम: 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

  1. 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 )
  2. 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
  3. Repeat step 2 above for every additional Filter F3 to Fn. (in our example there were only 2 filters, so we are already done)
  4. 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)
  5. 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

  1. generate SQL to select counts for all the combined sql WHERE stubs.
  2. 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




inner-join