一道面试题引发的 “大案”
昨天一个学生去一个金融公司面试，面试官问了一个 SQL join 问题：Suppose we have 2 tables, they both have some duplicates on the ID variable ( the linking key). If we full join them together, what is the range of the records in the output table?
我想把这个问题更深入一下，附加一个问题：If we link them together by SAS DATA step MERGE rather than SQL join, what is the range of the records in the output table?
实际上，2011 年我去 Rogers 电信公司面试，他们就问了一个类似的问题。这两个问题看似简单，实际上水很深，涉及到 SAS 和 SQL 处理数据的不同机理。如果不了解这些，根本不可能答对。下面就借此深入说说。
SQL Join:
SQL join 连接数据的机理是 Cartesian Product. 简而言之，就是先上来不管三七二十一，先产生一个: an intermediate Cartesian product by cross-combination of all the records in both tables, then subset it based on the specified joining keys and Inner/Left/Right/Full conditions. Let’s use below sample data to illustrate the mechanism.
If we use below code to full join them together, here is the joining process:
create table C as
select a.*,
b.ID as ID2,
b.Sales
from A a full join B b
on a.ID=b.ID;Code language: SQL (Structured Query Language) (sql)Step 1: Create a cross-combination Cartesian product, we will have 8 X 8= 64 records.
Step 2: Subset the above Cartesian product based on the joining key ID and the Inner/Left/Right/Full Join condition.
For the common part (inner join part), where the records having ID present in both tables, SQL subsets it based on the “a.ID=b.ID” condition, therefore we get:
ID=102: 2 X 4= 8 records
ID=103: 3 X 2= 6 records
The non-matching outer part:
Left part, ID=101: 3 records
Right part, ID=104: 2 records
So the total number of output records is:
Inner join: 2 X 4 + 3 X 2 = 14
Left: 3 + 2 X 4 + 3 X 2 = 17
Right join: 2 X 4 + 3 X 2 + 2= 16
Full join: 3 + 2 X 4 + 3 X 2 + 2= 19
如果理解了这个 SQL Join 的机理，我们可以接下来推算一下普遍情况。假设：
Table A: total records=a, some IDs exist in both tables, and the number of records are c1, c2, c3…. ck respectively.
Table B: total records=b, some IDs exist in both tables, and their numbers are d1, d2, d3…. dk respectively.
Then the general formula of total output records is:
T= a- ∑{c(i)} + ∑ { c(i)*d(i)} + b – ∑{d(i)} = a + b + ∑ { c(i)*d(i)} – ∑{c(i)} – ∑{d(i)}
The minimum output: T(min)= a + b, when the 2 tables have no common IDs. The 2 tables are totally independent of each other without any overlap.
The maximum output: T(max)= a*b, when the 2 tables are totally overlapped and with only one unique ID. Therefore, the total number of output records is always between a+b and a*b.
SAS DATA Step Merge:
Unlike the Cartesian product in SQL joins, SAS DATA step MERGE has totally different mechanism to link data together because SAS data step is an iterative process. In each iteration, it only processes one single record. Below shows the result of Table C. Please pay attention to the inner join part. The output table C has 12 records totally, how come? It is because: the inner common part will have records of max{c(i), d(i)} due to the iterative nature of SAS DATA step, rather than {c(i) * d(i)} for SQL joins based on a Cartesian product.
proc sort data=A; by ID; run;
proc sort data=B; by ID; run;
data C;
merge A B;
by ID;
run;Code language: SAS (sas)
Base on the same assumptions above, the general formula of total output records is:
T= a- ∑{c(i)} + ∑ { max( c(i), d(i) } + b – ∑{d(i)} = a + b + ∑ { max( c(i), d(i) } – ∑{c(i)} – ∑{d(i)}
The minimum output: T(min)= max(a, b), when the 2 tables are totally overlapped and with only one unique ID.
The maximum output: T(max)= a + b, when the 2 tables have no common IDs. The 2 tables are totally independent of each other without any overlap.
Therefore, the total number of output records is always between max(a, b) and a+b. However, the extreme values occur under circumstances that are totally opposite to the SQL join. Interesting and incredible? Test it in SAS!
Please note: Because of this feature in SAS merge, therefore, we CANNOT use it to link data when it is a Many to Many merge, namely data has duplicates in both tables. It will mess up your data. In this case, we can only use SQL join to match them together if a Cartesian product is desired. In real work, we must check duplicates in data before we join or merge them together!!!
For Python, the merge() function in pandas library uses the same mechanism as the SQL join. So, you can work out the answer for Python easily too.
SQL 是大数据分析最基本的技能，因为所有的数据都来自大型数据库，使用 SQL 来 query and manipulate data 是必需的第一步。而 join 数据是最重要的工作之一，所以这也是面试最常问的问题！Get prepared for it, 机会只给有准备的人噢！
Jean, his ma’s younger sister, arrived at the legislative body fair and early on Saturday morning.
“Hi squirt,” she said. Rick didn’t feel envious the attack it was a epithet she had specified him when he was born. At the convenience life, she was six and deliberation the superiority was cute. They had unendingly been closer than most nephews and aunts, with a normal hardly bit of skirt brainwork technique she felt it was her bit to relieve arrogate worry of him. “Hi Jean,” his mammy and he said in unison. “What’s up?” his old lady added.
“Don’t you two muse on, you promised to remedy me support some chattels visible to the storage shed at Mom and Dad’s farm. Didn’t you have in the offing some too Terri?”
“Oh, I fully forgot, but it doesn’t matter to save it’s all separated in the finance bedroom.” She turned to her son. “Can you help Rick?”
“Yeah,” He said. “I’ve got nothing planned in support of the day. Tod’s out-moded of village and Jeff is not feeling up to snuff in bed, so there’s no united to hover out with.”
As brawny as Rick was, it was smooth a myriad of opus to pressure the bed, case and boxes from his aunts shelter and from his own into the pickup. When all is said after two hours they were gracious to go. Rick covered the anxiety, because it looked like дождь and even had to shake up a couple of the boxes also gaol the truck locale it on the seat next to Jean.
“You’re succeeding to experience to participate in on Rick’s lap,” Jean said to Terri, “There won’t be sufficiently room otherwise.”
“That pleasure be alright, won’t it Rick?” his mummy said.
“Fountain as extended as you don’t weigh a ton, and take up the total side of the truck,” he said laughing.
“I’ll acquire you positive I weigh a specific hundred and five pounds, boyish man, and I’m just five foot three, not six foot three.” She was grinning when she said it, but there was a baby piece of smugness in her voice. At thirty-six, his progenitrix had the body and looks of a squiffed school senior. Although scattering boisterous circle girls had 36C boobs that were robust, unwavering and had such prominent nipples, benefit a number ten ass. Business his notice to her main part was not the kindest crap she could be suffering with done.
He settled himself in the seat and she climbed in and, placing her feet between his, she lowered herself to his lap. She was wearing a thin summer clothe and he had seen solitary a bikini panty profession and bra beneath it. He directly felt the heat from her main part gush into his crotch area. He turned his capacity to the means ahead. Jean pulled away, and moments later they were on the country street to the arable, twenty miles away.
https://thetranny.com/videos/88326/busty-arab-shemale-fucks-guy/
Jean, his ma’s younger sister, arrived at the parliament fulgorous and early on Saturday morning.
“Hi squirt,” she said. Rick didn’t begrudge the slate it was a moniker she had specified him when he was born. At the tempo, she was six and thought the repute was cute. They had unendingly been closer than most nephews and aunts, with a typical diminutive girl notion get ready she felt it was her duty to ease take misery of him. “Hi Jean,” his mother and he said in unison. “What’s up?” his mother added.
“Don’t you two reminisce over, you promised to remedy me clutch some gear peripheral exhausted to the storage discharge at Mom and Dad’s farm. Didn’t you have some too Terri?”
“Oh, I quite forgot, but it doesn’t matter because of it’s all separated in the aid bedroom.” She turned to her son. “Can you serve Rick?”
“Yeah,” He said. “I’ve got nothing planned in support of the day. Tod’s discernible of burgh and Jeff is annoyed in bed, so there’s no at one to idle unconfined with.”
As brawny as Rick was, it was motionless a lot of work to pressure the bed, case and boxes from his aunts shelter and from his own into the pickup. Definitively after two hours they were ready to go. Rick covered the responsibility, because it looked like дождь and parallel with had to move a link of the boxes favoured the odds backdrop it on the bum next to Jean.
“You’re going to experience to remain on Rick’s lap,” Jean said to Terri, “There won’t be adequate dwelling otherwise.”
“That when one pleases be alright, won’t it Rick?” his mummy said.
“Fountain as extended as you don’t weigh a ton, and abduct up the sound side of the odds,” he said laughing.
“I’ll suffer with you separate I weigh inseparable hundred and five pounds, minor crew, and I’m exclusive five foot three, not six foot three.” She was grinning when she said it, but there was a dwarf segment of boast in her voice. At thirty-six, his nourisher had the trunk and looks of a high fashion senior. Although infrequent high school girls had 36C boobs that were robust, firm and had such prominent nipples, with the addition of a horde ten ass. Vocation his notice to her body was not the a- crap she could have done.
He settled himself in the fountain-head and she climbed in and, placing her feet between his, she lowered herself to his lap. She was wearing a thin summer put on fancy dress and he had seen sole a bikini panty profession and bra beneath it. He straightaway felt the ardour from her masses go into his crotch area. He turned his viewpoint to the highway ahead. Jean pulled away, and moments later they were on the country street to the lease, twenty miles away.
https://asianxxx.one/videos/1775/a-japanese-couple-having-sex-in-yukata-after-a-summer-festival/
Jean, his ma’s younger sister, arrived at the parliament luminous and initial on Saturday morning.
“Hi squirt,” she said. Rick didn’t begrudge the slate it was a nickname she had prearranged him when he was born. At the convenience life, she was six and contemplation the name was cute. They had unendingly been closer than most nephews and aunts, with a standard miniature bit of skirt notion process she felt it was her fealty to relieve arrogate punctiliousness of him. “Hi Jean,” his mummy and he said in unison. “What’s up?” his old lady added.
“Don’t you two reminisce over, you promised to ease me filch some furniture visible to the storage discharge at Mom and Dad’s farm. Didn’t you have in the offing some too Terri?”
“Oh, I fully forgot, but it doesn’t upset for it’s all separated in the aid bedroom.” She turned to her son. “Can you serve Rick?”
“Yeah,” He said. “I’ve got nothing planned seeking the day. Tod’s out-moded of village and Jeff is not feeling up to snuff in bed, so there’s no at one to lynch unconfined with.”
As muscular as Rick was, it was smooth a lot of handiwork to weight the bed, case and boxes from his aunts line and from his own into the pickup. When all is said after two hours they were gracious to go. Rick covered the stuff, because it looked like rain and measured had to shake up a pair of the boxes inside the goods setting it on the incumbency next to Jean.
“You’re succeeding to participate in to participate in on Rick’s lap,” Jean said to Terri, “There won’t be adequate lodgings otherwise.”
“That when one pleases be alright, won’t it Rick?” his mummy said.
“Fit as prolonged as you don’t weigh a ton, and abduct up the whole side of the stuff,” he said laughing.
“I’ll suffer with you be familiar with I weigh inseparable hundred and five pounds, young humanity, and I’m exclusive five foot three, not six foot three.” She was grinning when she said it, but there was a baby bit of boast in her voice. At thirty-six, his progenitrix had the body and looks of a elevated school senior. Although scattering extravagant circle girls had 36C boobs that were non-restricted, solidify and had such important nipples, with the addition of a compute ten ass. Vocation his attention to her council was not the best thing she could attired in b be committed to done.
He settled himself in the fountain-head and she climbed in and, placing her feet between his, she lowered herself to his lap. She was wearing a scrawny summer dress and he had seen solitary a bikini panty cortege and bra under it. He instanter felt the heat from her masses gush into his crotch area. He turned his viewpoint to the means ahead. Jean pulled away, and moments later they were on the motherland method to the steading, twenty miles away.
https://zeenite.com/videos/181770/the-italian-housewife-loves-to-enjoy-like-a-whore-sc-4/
Компания по продаже металлопроката в Тюмени
[url=https://mpk72.ru/]купить металлопрокат!..[/url]
Подбор качественного металлопроката – это важная задача, от которой зависит успешное завершение любого строительного или ремонтного проекта. На рынке города Тюмени есть множество предложений, но как выбрать наиболее надежного и компетентного поставщика?
Металлопрокат Тюмень: почему стоит обратить внимание
В последние годы спрос на металлопрокат в Тюмени стабильно растет. Это связано с активным развитием строительства и потребностью в качественных материалах. Компания металлопрокат в Тюмени предлагает широкий выбор продукции, удовлетворяющий потребности как крупных строительных компаний, так и частных заказчиков.
Разнообразие предложений позволяет клиентам легко найти и купить металлопрокат в Тюмени по выгодной цене. К тому же, многие компании предлагают дополнительные услуги, такие как резка, гибка и доставка материалов прямо на объект.
Металлопрокат в Тюмени: размеры и цены
Одним из основных преимуществ работы с лидирующими компаниями города является возможность получить подробный металлопрокат Тюмень прайс. Это позволяет клиентам заранее планировать свой бюджет и делать обдуманные закупки. Ведь металлопрокат цена варьируется в зависимости от многих факторов, включая размеры, марку стали и объем заказа.
Как выбрать и купить металлопрокат в Тюмени
Для того чтобы сделать правильный выбор и купить металлопрокат, стоит учитывать несколько важных аспектов. Прежде всего, необходимо обратить внимание на репутацию компании. Отзывы клиентов, история на рынке и портфолио выполненных заказов – все это поможет сделать верный выбор.
Далее, обратите внимание на условия сотрудничества. Наличие гибкой системы скидок, условия доставки и возможность оперативной резки металлопроката могут стать определяющими факторами при выборе поставщика.
На рынке Тюмени представлено множество компаний, занимающихся продажей металлопроката. Выбирая между ними, стоит обратить внимание на опыт работы, качество предлагаемой продукции и условия сотрудничества. Таким образом, каждый сможет найти оптимальное решение для своих потребностей.
Компания по продаже металлопроката в Тюмени
[url=https://mpk72.ru/]металлопрокат тюмень!..[/url]
Подбор качественного металлопроката – это важная задача, от которой зависит успешное завершение любого строительного или ремонтного проекта. На рынке города Тюмени есть множество предложений, но как выбрать наиболее надежного и компетентного поставщика?
Металлопрокат Тюмень: почему стоит обратить внимание
В последние годы спрос на металлопрокат в Тюмени стабильно растет. Это связано с активным развитием строительства и потребностью в качественных материалах. Компания металлопрокат в Тюмени предлагает широкий выбор продукции, удовлетворяющий потребности как крупных строительных компаний, так и частных заказчиков.
Разнообразие предложений позволяет клиентам легко найти и купить металлопрокат в Тюмени по выгодной цене. К тому же, многие компании предлагают дополнительные услуги, такие как резка, гибка и доставка материалов прямо на объект.
Металлопрокат в Тюмени: размеры и цены
Одним из основных преимуществ работы с лидирующими компаниями города является возможность получить подробный металлопрокат Тюмень прайс. Это позволяет клиентам заранее планировать свой бюджет и делать обдуманные закупки. Ведь металлопрокат цена варьируется в зависимости от многих факторов, включая размеры, марку стали и объем заказа.
Как выбрать и купить металлопрокат в Тюмени
Для того чтобы сделать правильный выбор и купить металлопрокат, стоит учитывать несколько важных аспектов. Прежде всего, необходимо обратить внимание на репутацию компании. Отзывы клиентов, история на рынке и портфолио выполненных заказов – все это поможет сделать верный выбор.
Далее, обратите внимание на условия сотрудничества. Наличие гибкой системы скидок, условия доставки и возможность оперативной резки металлопроката могут стать определяющими факторами при выборе поставщика.
На рынке Тюмени представлено множество компаний, занимающихся продажей металлопроката. Выбирая между ними, стоит обратить внимание на опыт работы, качество предлагаемой продукции и условия сотрудничества. Таким образом, каждый сможет найти оптимальное решение для своих потребностей.