| AWS |
AWS Management Tools Blog |
How CDW manages AWS support cases for multiple AWS managed services (AMS) Accelerate accounts |
https://aws.amazon.com/blogs/mt/how-cdw-manages-aws-support-cases-for-multiple-aws-managed-services-ams-accelerate-accounts/
|
How CDW manages AWS support cases for multiple AWS managed services AMS Accelerate accountsCDW is an AWS Advanced Tier partner with six partner programs and AWS Customer launches Furthermore it has partnered with AWS Managed Services AMS to provide a competitive advantage that further propels the quality and delivery of end to end IT solutions CDW Cloud Managed Services powered by AMS helps you deploy deliver and manage applications … |
2022-10-24 20:12:18 |
| AWS |
AWS Mobile Blog |
Supply Chain Automation using IoT and Location-Based Services |
https://aws.amazon.com/blogs/mobile/supply-chain-automation-using-iot-and-location-based-services/
|
Supply Chain Automation using IoT and Location Based ServicesKeeping track of distributed assets across facilities and geographic locations is cumbersome Businesses use various applications and approaches to maintain and manage asset lifecycles which can get complex and tedious This post will teach you a moderation approach using AWS IoT Core and Amazon Location Service Amazon Location To build the solution you ll utilize an … |
2022-10-24 20:31:44 |
| AWS |
AWS |
Create a Container Product in AWS Marketplace - Part 3 | Amazon Web Services |
https://www.youtube.com/watch?v=Nsu8n-rikAc
|
Create a Container Product in AWS Marketplace Part Amazon Web ServicesIn this video you ll see you ll see Part of how to create a container product on AWS Marketplace By listing your container product on AWS Marketplace you can grow your customer base protect your product s Intellectual Property and improve efficiency and reliability This video is part of a three part series For more information on this topic please visit the resources below Subscribe More AWS videos More AWS events videos ABOUT AWSAmazon Web Services AWS is the world s most comprehensive and broadly adopted cloud platform offering over fully featured services from data centers globally Millions of customers ーincluding the fastest growing startups largest enterprises and leading government agencies ーare using AWS to lower costs become more agile and innovate faster AWS AmazonWebServices CloudComputing |
2022-10-24 20:18:32 |
| AWS |
AWS |
Provision and Deploy Amazon AppStream 2.0 using AWS Service Catalog | Amazon Web Services |
https://www.youtube.com/watch?v=kSLHf98H7tg
|
Provision and Deploy Amazon AppStream using AWS Service Catalog Amazon Web ServicesIn this video you ll see how to provision and deploy Amazon AppStream using AWS Service Catalog With this solution you can create a template for a secure and compliant AppStream deployment publish the template as a product in AWS Service Catalog and allow authorized users to launch an AppStream stack on their own For more information on this topic please visit the resource below Subscribe More AWS videos More AWS events videos ABOUT AWSAmazon Web Services AWS is the world s most comprehensive and broadly adopted cloud platform offering over fully featured services from data centers globally Millions of customers ーincluding the fastest growing startups largest enterprises and leading government agencies ーare using AWS to lower costs become more agile and innovate faster AmazonAppStream AmazonAppStream AWSServiceCatalog AppStream AWS AmazonWebServices CloudComputing |
2022-10-24 20:01:27 |
| 海外TECH |
MakeUseOf |
10 Essential Rules Every Technical Writer Must Follow |
https://www.makeuseof.com/rules-technical-writer-follow/
|
writer |
2022-10-24 20:45:14 |
| 海外TECH |
MakeUseOf |
Should You Lie About Your Age When Creating a Social Media Account? |
https://www.makeuseof.com/should-you-lie-about-age-create-social-media-account/
|
social |
2022-10-24 20:31:14 |
| 海外TECH |
MakeUseOf |
What Is Netflix’s Profile Transfer and How Does It Work? |
https://www.makeuseof.com/what-is-netflix-profile-transfer-how-it-works/
|
profile |
2022-10-24 20:10:27 |
| 海外TECH |
DEV Community |
Trends in Developer Jobs: A Meta Analysis of Stack Overflow Surveys |
https://dev.to/bootdotdev/trends-in-developer-jobs-a-meta-analysis-of-stack-overflow-surveys-3aeo
|
Trends in Developer Jobs A Meta Analysis of Stack Overflow SurveysI m really interested in the trends we see in the software engineering job market Sometimes it s really hard to tell a cohesive and accurate narrative about what s happening because it just happens so dang fast and very few people are collecting data on the matter For example here are some questions that I d like to know the answer to Does DevOps and increased cloud usage mean fewer traditional ops jobs Do things like Firebase and Supabase mean fewer back end jobs Do things like tailwind mean fewer designers Are we moving into more specialist or more generalist roles Are people still specializing as DBAs I aggregated all the answers about developer employment from the last years of Stack Overflow surveys in order to start being able to answer some of these questions If you re curious about the job outlook for developers hopefully this is helpful data First a note on the dataAll the numbers in the tables represent the percentage of survey takers who identified themselves as a given job type The questions asked by SO have changed over the years so we should take that as a giant grain of salt I ve normalized the answers For example developer back end and back end web developer I ve grouped together as backend Stack overflow allowed people to select many job titles after before that it appears they were limited to one In some years developers had more job options to pick from I ve thrown away a lot of off topic job types like enterprise services developer and elected official This is only Stack Overflow data so it s biased by the size of their user base each year That said let s get into the data I ll drop the script I wrote to create the aggregate data as well as a link to the raw data at the bottom of this article Web Trends Full Stack Front end and Back endyearfullstackfrontendbackendIn the ratio of front end developers to full stack developers was while the ratio of back end developers to full stack developers was The trend of more full stack less specialization continues through the entire ten years of data It s important to note that in the survey changed drastically which is why we see a big change in the numbers Interpretation It seems reasonable to conclude that the trend for the last decade has been that there are a shrinking percentage of developers who do only front end work or only back end work More and more are doubling as full stack engineers Keep in mind this also might be that there have been more small to mid sized companies over the years Smaller companies typically require more generalists but now I m just guessing Interpretation The ratio of front end back end engineers has stayed fairly consistent with there being slightly less than twice as many back end engineers than front end engineers This actually surprised me I expected the ratio of front end to back end engineers to be closer throughout the years IT Operations Trends Back end DevOps and Traditional OpsyearbackenddevopsopsIn my script I tried to split more traditional ops roles into the ops category and the devops stuff into the devops role For example SRE I ve considered as devops while systems administrator is ops Caveat on the data for What the hell happened in The data honestly just seems broken I dug through the data manually because according to the website they claim of web developers said they were back end and of respondents claimed to be web developers At the moment it s looking to me like they must have had some qualifiers on those numbers because it s just not adding up on my end I m going to exclude from my analysis in the interpretations Interpretation DevOps appears to be gaining ground on traditional ops In no one was identifying themselves as a devops person but by and the numbers are looking very similar It s worth pointing out that in the devops numbers actually eclipsed the ops numbers for a single year My best guess is that was about when many companies started simply rebranding their ops teams as devops teams in order to look cool It s hard to care too much about these numbers because devops is mostly being done wrong in my opinion I don t trust that the ops titles and the devops titles are all that different at most companies Interpretation Devops seems to be trending down the least in recent years in fact it had a nice jump in However if you look at devops and ops together then the category is still trending down a bit Interestingly ops has been trending down from the start while back end was trending up until when the trend reversed and it s been down since At first I assumed we re simply seeing the same trend that we saw in web development more generalists less specialists However I grew skeptical because when I looked across all job categories I noticed nearly all of them were trending down which clearly can not be the case when we re looking at percentages it s a zero sum game I decided to add a new section to my script to dig in further I calculated how many jobs on average each survey taker was laying claim to and got this data yearbackenddevopsopsavg jobs per userIt appears that from developers were restricted to only submitting a single answer which helps to account for the super low numbers However from gt the average number of jobs per user went down which is antithetical to the more generalists theory It s also worth pointing out that as the years went on Stack OVerflow actually added more specialized categories which I then took the liberty of grouping into these broader groups So there s actually good evidence that developers are specializing more or at the very least that there are more possible ways in which one can specialize That said even after looking at this data I think there is still a good case to be made that back end developers will be doing more and more devops work especially at smaller companies Data trends Data science data engineering and back endyeartype data sciencedata engineerbackend InterpretationIt s super interesting to me that data engineering really only started to appear in the survey data in Until then I m guessing that role was swallowed up by back end engineers and data scientists That new specialization is certainly interesting Machine learning has absolutely grown over the last decade but it looks like there may have been a bit of a hype bubble in The Rest of the DataI ve talked about my personal interpretations regarding the data that I found most the interesting but here s all the data I aggregated so you can inspect it yourself yearavg jobs per userfullstackfrontendbackenddevopsopsmobiledesktopembeddeddata sciencedata engineergamemanagementqaeducationdesignanalystmarketerignore Raw CSV DataHere s a link to the raw CSV data on Stack Overflow My ScriptHere s the full Python script I used to crunch the numbers Sorry for the code sloppiness I didn t sink a ton of time into the code The most interesting part of the script is probably the get mapped job function near the bottom that s where I aggregate all of the many job types reported by stack overflow users into the few I included in the chart import csvoutpath csv out csv type devops devops type ops ops type backend backend type frontend frontend type mobile mobile type fullstack fullstack type desktop desktop type embedded embedded type data science data science type ignore ignore type management management type education education type design design type marketer marketer type data engineer data engineer type game game type analyst analyst type qa qa def main files out dict jobs per user dict for f tup in files counts path f csv f tup csv print f generating report for path out dict f tup with open path r as csvfile rows csv reader csvfile delimiter count rows cpy jobs per user for row in rows count rows cpy append row for row in rows cpy try jobs get jobtext from cells f tup row mapped jobs set for job in jobs mapped jobs add get mapped job job jobs per user append mapped jobs for mapped job in mapped jobs if mapped job not in counts counts mapped job counts mapped job except Exception as e print e avg jobs per user for user jobs in jobs per user avg jobs per user len user jobs jobs per user dict f tup round avg jobs per user len jobs per user for job in counts counts job count counts job counts job round counts job out dict f tup counts write out out dict jobs per user dict def get jobtext from cells indexes row if len indexes return job texts for i in indexes cell row i cell job texts cell split job texts cell job texts return job textsdef write out out dict jobs per user dict types type fullstack type frontend type backend type devops type ops type mobile type desktop type embedded type data science type data engineer type game type management type qa type education type design type analyst type marketer type ignore with open outpath w as csvfile w csv writer csvfile w writerow year avg jobs per user types for year in out dict row year jobs per user dict year for t in types row append out dict year t if t in out dict year else w writerow row def get mapped job job job job lower strip if job return type ignore if job devops specialist return type devops if job designer return type design if job c suite executive return type management if job analyst or consultant return type analyst if job back end developer return type backend if job windows phone return type mobile if job i don t work in tech return type ignore if job growth hacker return type marketer if job desktop developer return type desktop if job analyst return type analyst if job executive vp of eng cto cio etc return type management if job mobiledevelopertype return type mobile if job engineer data return type data engineer if job graphics programmer return type game if job systems administrator return type ops if job developer game or graphics return type game if job desktop software developer return type desktop if job nondevelopertype return type ignore if job elected official return type ignore if job engineering manager return type management if job web developer return type fullstack if job machine learning specialist return type data science if job data or business analyst return type analyst if job devtype return type fullstack if job response return type ignore if job developer qa or test return type qa if job machine learning developer return type data science if job developer front end return type frontend if job database administrator return type ops if job android return type mobile if job webdevelopertype return type fullstack if job blackberry return type mobile if job system administrator return type ops if job mobile developer android return type mobile if job developertype return type fullstack if job ios return type mobile if job developer with a statistics or mathematics background return type ignore if job qa or test developer return type qa if job educator or academic researcher return type education if job engineer site reliability return type devops if job marketing or sales professional return type marketer if job student return type ignore if job back end web developer return type backend if job educator return type education if job front end developer return type frontend if job developer desktop or enterprise applications return type desktop if job senior executive vp return type management if job occupation return type ignore if job scientist return type ignore if job developer full stack return type fullstack if job graphic designer return type design if job developer embedded applications or devices return type embedded if job embedded application developer return type embedded if job quality assurance return type qa if job graphics programming return type game if job senior executive c suite vp etc return type management if job it staff system administrator return type ops if job business intelligence or data warehousing expert return type data engineer if job full stack web developer return type fullstack if job developer mobile return type mobile if job front end web developer return type frontend if job desktop applications developer return type desktop if job other please specify return type ignore if job mobile developer return type mobile if job devops return type devops if job enterprise level services developer return type ignore if job data scientist return type data science if job executive vp of eng cto cio etc return type management if job mobile developer ios return type mobile if job game or graphics developer return type game if job which of the following best describes your occupation return type ignore if job other return type ignore if job desktop or enterprise applications developer return type desktop if job c suite executive ceo cto etc return type management if job embedded applications devices developer return type embedded if job product manager return type ignore if job mobile application developer return type mobile if job mobile developer windows phone return type mobile if job data scientist or machine learning specialist return type data science if job educator or academic return type education if job embedded applications or devices developer return type embedded if job quality assurance engineer return type qa if job enterprise level services return type ignore if job full stack developer return type fullstack if job na return type ignore if job academic researcher return type education if job manager of developers or team leader return type management if job marketing or sales manager return type marketer if job developer back end return type backend if job full stack web developer return type fullstack if job designer or illustrator return type design if job programmer return type ignore if job developer return type ignore if job manager return type management if job engineer return type ignore if job sr developer return type ignore if job full stack overflow developer return type fullstack if job ninja return type ignore if job mobile dev android ios wp amp multi platform return type mobile if job expert return type ignore if job rockstar return type ignore if job hacker return type ignore if job guru return type ignore if job self identification return type ignore if job occupation group return type ignore if job cloud infrastructure engineer return type devops if job project manager return type management if job security professional return type ops if job blockchain return type backend if job mathematics developers data scientists machine learning devs amp devs with stats amp math backgrounds return type data science raise Exception f job not mapped job main |
2022-10-24 20:16:44 |
| 海外TECH |
DEV Community |
SQL + Python + Spark for Data Science |
https://dev.to/surendraarivappagari/sql-python-spark-for-data-science-37h5
|
SQL Python Spark for Data Science Table of Content In this SQL tutorial we will be learning below concepts As I ve used Jupyter Notebooks for writing this blog I ve used Pyspark for interactive outputs so just to skip Pyspark stuff directly jump to Section D for SQL concepts Note I ve used some dummy data so that we can cover all SQL concepts with all edge cases Prerequisites A Pyspark Connection skip this section B Create dataframe by reading files and datatype conversion skip this section C Create TempView Table Data overview and size count skip this section D Select statement AS LIMIT COUNT DISTINCT E Where clause BETWEEN LIKE IN AND OR F Order By G Upper Lower Length functions H Concatenation BooleanExpression TRIM functions I SUBSTRING REPLACE POSITION functions J Aggregation functions K GROUP BY HAVING L Sub Queries M Correlated sub queries N Case statement O Joins INNER LEFT RIGHT FULL CROSS P Union Union all Except Q Window functions Conclusion Prerequisites Basic understanding of rows columns in table or excel sheet will be enough to understand the SQL concepts To get more insight about the data we have by using SQL Structure Query Language we can get quick detailed analysis Note Here we are using Python Spark SQL to get the output Spark Is In Memory processing engine from Apache for Big data analysis Python As a programming language scripting language we are using with Spark SQL for querying data to get required outputs In every section just see the sql query line to understand the concepts Rest of the codes are written in Pyspark to print the output in Jupyter notebooks In general if we have any software for RDBMS we no need to worry about the Pyspark codes You can directly jump to section to begin the SQL concepts A Pyspark Connection skip this section Connection part for Pyspark and importing required packages import pandas as pdimport numpy as npfrom pyspark sql import SparkSessionfrom pyspark sql types import from pyspark sql functions import from pyspark sql import spark SparkSession builder appName Pyspark with SQL getOrCreate conf spark sparkContext conf setAll spark driver memory g spark executor memory g spark executor num spark network timeout Explanation Spark is in memory data processing analytics engine Spark is mainly used in Bigdata platforms to process the large data in less running time It is having parallel processing capacity due to this we can given required number of executors just like threads in multi threading concepts to distribute the data parallelly and execute process the largge amount of data in less time Spark offers languages to write the frameworks These are Python Java R Scala In this blog we are using Python Spark so called Pyspark Above syntax is used to create spark session so that we can able to query the SQL commands directly using Python and we are intimating how many executors Threads in multi threading required in this session For now we can skip this section and jump to section D B Create dataframe by reading files and datatype conversion skip this section In this blog we are having datasets tables in SQL mentioned below Before creating a dataset table we are assigning the datatype for each column in each table Here with the help of Pandas Pyspark dataframes we are defining datatypes for each column Lets work on each table Student table contains all Student related information Excel file link here University table contains all University related information Excel file link here Company table contains all Company related information Excel file link here Year Month Day table contains sample data for Date type related information Excel file link here B Create Student dataframe and define datatypes in pyspark student dfpd pd read excel r Table Source Student Placement Table xlsx schema student StructType StructField ID IntegerType False StructField Name StringType False StructField Gender StringType False StructField DOB DateType False StructField Location StringType True StructField University StringType False StructField Salary DoubleType False StructField Company StringType False StructField Email StringType False student dfps spark createDataFrame student dfpd schema student Explanation Line Using pd read excel method reading the excel file and creating pandas dataframe Line Using StructType and StructField we are defining the schema for the dataset datatype for each column names and whether it is nullable or not Ex For ID column we are informing that it is integer type and it cannot be null means ID column shouldn t have missing data in it Line Using spark createDataFrame method with data schema parameters we are creating Pyspark dataframe B Create University dataframe and define datatypes in pyspark university dfpd pd read excel r Table Source University Table xlsx schema university StructType StructField University StringType False StructField MinSalary StringType False StructField PlayGround StringType False StructField Total Students IntegerType False university dfps spark createDataFrame university dfpd schema university Explanation Line Using pd read excel method reading the excel file and creating pandas dataframe Line Using StructType and StructField we are defining the schema for the dataset datatype for each column names and whether it is nullable or not Ex For Total Students column we are informing that it is integer type and it cannot be null means Total Students column shouldn t have missing data in it Line Using spark createDataFrame method with data schema parameters we are creating Pyspark dataframe B Create Company dataframe and define datatypes in pyspark company dfpd pd read excel r Table Source Company Table xlsx schema company StructType StructField Company StringType False StructField Total Employes IntegerType False StructField Total Products IntegerType False StructField Hike Per Anum IntegerType False StructField WHF Office StringType False company dfps spark createDataFrame company dfpd schema company Explanation Line Using pd read excel method reading the excel file and creating pandas dataframe Line Using StructType and StructField we are defining the schema for the dataset datatype for each column names and whether it is nullable or not Ex For Total Employes column we are informing that it is integer type and it cannot be null means Total Employes column shouldn t have missing data in it Line Using spark createDataFrame method with data schema parameters we are creating Pyspark dataframe B Create Year Month Day dataframe and define datatypes in pyspark year month day dfpd pd read excel r Table Source Year Month Day xlsx schema year month day StructType StructField Year IntegerType False StructField Month StringType False StructField Day IntegerType False StructField Salary IntegerType False year month day dfps spark createDataFrame year month day dfpd schema year month day Explanation Line Using pd read excel method reading the excel file and creating pandas dataframe Line Using StructType and StructField we are defining the schema for the dataset datatype for each column names and whether it is nullable or not Ex For Year column we are informing that it is integer type and it cannot be null means Year column shouldn t have missing data in it Line Using spark createDataFrame method with data schema parameters we are creating Pyspark dataframe C Create TempView Table Data overview and size count skip this section In this section we are creatingPyspark TempView just like Table in SQL and cross checking the table schema sample data and record count Below code snippet is for creating temporary views in pyspark with the help of pyspark dataframe we created in previous sections student dfps createOrReplaceTempView Student Table university dfps createOrReplaceTempView University Table company dfps createOrReplaceTempView Company Table year month day dfps createOrReplaceTempView Year Month Day Table Explanation Here we are creating TempViews for all above pyspark dataframes so that in coming sections we can directly work on SQL queries In each line left side we have pyspark dataframe name In Pyspark we have createOrReplaceTempView method to create Table like structure so that we can work on SQL queries to get the required information EX Lets take first line where student dfps is the pyspark dataframe Student Table is the pyspark temporary view where we can apply all SQL stuff on top of it Now lets check the each table schema record count and sample data C Student Table Schema Row count Data Overview print Student Table Schema student dfps printSchema print total count of records print Total records of Student Table student dfps count n nStudent Table Data List all the records in tablesql query SELECT FROM Student Table spark sql sql query show Explanation Command Using printSchema method we can able to view the schema for all columns in the dataframe with nullable check Command Using count method we can check the row count for pyspark dataframe Command sql query is a python string variable it contains actual SQL query to perform Command Using spark sql method we can send the actual SQL command to execute and provide the output show method is used to limit the records rows to be printed If we skip to provide the value bydefault it will print first records only It is like LIMIT clause in SQL If dataframe record count is lessthan given parameter or default count then it will only print the available records in table Output C Student Table Schema Row count Data Overview print University Table Schema university dfps printSchema print total count of records print Total records of University Table university dfps count n n University Table Data List all the records in tablesql query SELECT FROM University Table spark sql sql query show Explanation Command Using printSchema method we can able to view the schema for all columns in the dataframe with nullable check Command Using count method we can check the row count for pyspark dataframe Command sql query is a python string variable it contains actual SQL query to perform Command Using spark sql method we can send the actual SQL command to execute and provide the output show method is used to limit the records rows to be printed If we skip to provide the value bydefault it will print first records only It is like LIMIT clause in SQL If dataframe record count is lessthan given parameter or default count then it will only print the available records in table Output C Company Table Schema Row count Data Overview print Company Table Schema company dfps printSchema print total count of records print Total records of Company Table company dfps count n n Company Table Data List all the records in tablesql query SELECT FROM Company Table spark sql sql query show Explanation Command Using printSchema method we can able to view the schema for all columns in the dataframe with nullable check Command Using count method we can check the row count for pyspark dataframe Command sql query is a python string variable it contains actual SQL query to perform Command Using spark sql method we can send the actual SQL command to execute and provide the output show method is used to limit the records rows to be printed If we skip to provide the value bydefault it will print first records only It is like LIMIT clause in SQL If dataframe record count is lessthan given parameter or default count then it will only print the available records in table Output C Year Month Day Table Schema Row count Data Overview print Year Month Day Table Schema year month day dfps printSchema print total count of records print Total records of Company Table year month day dfps count n n Company Table Data List all the records in tablesql query SELECT FROM Year Month Day Table spark sql sql query show Explanation Command Using printSchema method we can able to view the schema for all columns in the dataframe with nullable check Command Using count method we can check the row count for pyspark dataframe Command sql query is a python string variable it contains actual SQL query to perform Command Using spark sql method we can send the actual SQL command to execute and provide the output show method is used to limit the records rows to be printed If we skip to provide the value bydefault it will print first records only It is like LIMIT clause in SQL If dataframe record count is lessthan given parameter or default count then it will only print the available records in table Output D Select statement AS LIMIT COUNT DISTINCT Select statement is used to select choose or print the few columns or all columns from the table Lets explore all edge cases with select statement used to select all the columns from the table AS used to alias the column name in output console LIMIT used to limit the records in output console for mentioned columns count used to print the valid record count from the table DISTINCT used to fetch unique values from the table for mentioned column s D Select only few columns LIMIT print D Print only ID NAME GENDER columns sql query SELECT ID NAME GENDER FROM Student Table LIMIT spark sql sql query show Explanation Here we have mentioned specific column names we want to print in output rather all the columns from the table with LIMIT clause so that number of records will be filters to given number This will help in selecting required columns and provide the output to business in real time Output D Select all the columns LIMIT print D Print all columns from table only rows sql query SELECT FROM Student Table LIMIT spark sql sql query show Explanation By using in the select statement we can fetch all the columns available in table to output By using LIMIT we are restricting number of records in output for given number Here it is rows with all the columns Output D Alias names for columns print D Alias name for ID Name columns sql query SELECT ID as ID Number Name as Name of Student FROM Student Table spark sql sql query show Explanation Sometimes table might contains very short column names which cannot be understand or sometimes column name might be very lengthy which can be understand in short name in this case we can use Alias names for the columns using AS keyword Here ID column is printed as ID Number column Name column printed as Name of Student column This Alias concept will give some temporary names for the columns unless we use this concept in inner queries so that original column names will be remain same for the table Output D Counting number of records print D Print total records in given table sql query SELECT count as Total Count FROM Student Table spark sql sql query show Explanation Here we are using count as a column name called Total Count This will give us the number of valid not null records available in the table If we use count in the select query then it will valid not null records in the first column of the table This will be very handy to check how many missing values present in given column from the table If we missing giving number and mentioned as it means that it will check all the columns missing data for all rows if and only if in single row all columns data having null then only it will skip that record while counting and print the valid records from the table Output D Select some random text print D Print some sample text using select statement sql query SELECT Hello I am SQL as Column Name spark sql sql query show Explanation Selecting some random text will be very useful in real time when we apply UNION UNION ALL statements and one of the table having more columns than other in that case we case this kind of temporary data and alias to the matching column from other table so that UNION statements will not impact and gives the output Here Hello I am SQL is the data in the column called Column Name Output D Distinct in select statement print D A Without Distinct statement it will list all records in that column s sql query SELECT Location FROM Student Table spark sql sql query show print D B With Distinct statement it will list only distinct records in that column s sql query SELECT DISTINCT Location FROM Student Table spark sql sql query show Explanation If we want to check for unique values in a single column or unique values with combination of multiple columns we can use DISTINCT keyword Ex In Location column we have value Chennai has been repeated times After applying DISTINCT we could see only once in the output Output E Where clause BETWEEN LIKE IN AND OR With select statement we can restrict the number of columns to be printed With where clause we can restrict the number of records rows to be printed Based on some condition s if we want to filter the data we can use WHERE clause This is totally different than LIMIT clause because with LIMIT we cannot filter the data based on conditions it simply restrict the row count for given numbers WHERE clause is frequently used in real time analysis because business table will be containing all sort of data and based on filter conditions we can get the required data out of it Lets explore different ways to use WHERE clause BETWEEN To filter the data with given range LIKE To filter the data for given data pattern IN To filter the data for given list of values AND To filter the data for both conditions becomes true OR To filter the data for any one conditions becomes true E WHERE clause print E Print records only from Banglore location sql query SELECT FROM Student Table WHERE Location Banglore spark sql sql query show Explanation If the scenario is filter the data for Banglore location we can use the above syntax to get required output Output E WHERE clause BETWEEN print E Print records only ID range from to Inclusive sql query SELECT FROM Student Table WHERE ID BETWEEN AND spark sql sql query show Explanation Here by using BETWEEN command with WHERE clause we could able to print the ID s from to inclusively Even if few ID s are present in table then those condition matching records will be printed Output E WHERE clause LIKE print E Print records only Company value contains soft sql query SELECT FROM Student Table WHERE COMPANY LIKE soft spark sql sql query show Explanation By using LIKE command with WHERE clause we can able to filter the data with given patterns for any columns data Here we could able to print the company names which have soft substring within COMPANY column value Output E WHERE clause IN print E Print records only Name in given list AAA GGG KKK sql query SELECT FROM Student Table WHERE NAME IN AAA GGG KKK spark sql sql query show Explanation With BETWEEN command we will give the range for the column to be filtered But in IN command we will be giving the list of values to be checked for WHERE condition Output E WHERE clause AND print E Print records from Banglore location and Microsoft company sql query SELECT FROM Student Table WHERE LOCATION Banglore AND COMPANY Microsoft spark sql sql query show Explanation With WHERE clause if we use AND command then given all conditions should be matched for the output Both the conditions should be TRUE Ex Here the conditions are LOCATION Banglore AND COMPANY Microsoft and in the all the records with there two conditions will be printed Output E WHERE clause OR print E Print records from Banglore location or Microsoft company sql query SELECT FROM Student Table WHERE LOCATION Banglore OR COMPANY Microsoft spark sql sql query show Explanation Difference between AND OR is in AND command if and only if given conditions should be matched then only that record will be printed in the output But in OR command either of the conditions matches then matching record will be printed in the output Ex Here Even if Location Chennai also printed in the output because in that record Company Microsoft so here one condition is matching and so that record will be printed in the output Output F Order By In real time data analysis ordering the data based on one column or combination of columns has been frequently used for business solutions By default ORDER BY clause will sort the data in ascending order or explicitly we can use the keyword called ASC ASC will sort the data in ascending order for given column s DESC will sort the data in descending order for given column s F ORDER BY ASC print F Sort by Salary Accending order top records sql query SELECT FROM Student Table ORDER BY Salary ASCLIMIT spark sql sql query show Explanation By default with ORDER BY clause follows ascending order Just for our understanding we can use ASC keyword after the column name s In Ascending order integers floats will start from to n and string values will be sorted from A to Z If any special characters there in the column values then ASCII values will come to the picture Ex In below example we are sorting the salaries in ascending order Output F ORDER BY DESC print F Sort by Name Descending order top records sql query SELECT FROM Student Table ORDER BY Name DESC LIMIT spark sql sql query show Explanation By using DESC key word we can sort the data in descending order In this case integers floats will start from n to and string values will be sorted from Z to A Ex In below we are sorting Names in descending order Output G Upper Lower Length functions For string valued columns we can apply these functions Lets see the below description for given function UPPER string data will be converted to upper case LOWER string data will be converted to lower case LENGTH for string data it will give the character length in numbers Lets explore these functions with real time data print G Apply Upper Lower Length functions to columns sql query SELECT DISTINCT COMPANY UPPER COMPANY LOWER COMPANY LENGTH COMPANY FROM Student Table spark sql sql query show Explanation In this example we used COMPANY column s data applied DISTINCT keyword to restrict the duplicates In first column data will come as is In second column data will be converted into upper case In third column data will be converted into lower case In forth column we get the number of characters in each record data Ex Apple is having characters Here empty spaces will also be considered for counting Output H Concatenation BooleanExpression TRIM functions Lets check on some useful functions which are frequently used in real time data analysis Concatenation To club multiple columns or strings into single column BooleanExpression After applying boolean expression on some column to get True or False values TRIM To eliminate the spaces between string columns H Concatenation print H Concatenation using symbol and club multiple columns into single column sql query SELECT Name University I am Name from University as Self Intro FROM Student Table LIMIT spark sql sql query show Explanation By using concatenation symbol we can club Name Universitycolumns and few strings into Self Intro column Here I am Name from University is mapped to single column Output H Boolean Expression print H Boolean Expression with some condition sql query SELECT ID NAME SALARY Salary gt As IsSalaryGraterThanK FROM Student Table LIMIT spark sql sql query show Explanation By using conditional operators called gt gt lt lt we can apply conditions and create a new column to get the boolean values based on given condition Here in below example we comparing the Salary column if it is more than or not If salary grater than we will get true else false into new alias column we created called IsSalaryGraterThanK Output H TRIM function print H Trim function used to remove extra spaces in column s data sql query SELECT Google AS ExtraSpaces LENGTH Google AS Len ExtraSpaces TRIM Google AS TrimApplied LENGTH TRIM Google AS Len TrimApplied RTRIM Google AS RTrimApplied LENGTH RTRIM Google AS Len RTrimApplied LTRIM Google AS LTrimApplied LENGTH LTRIM Google AS Len LTrimApplied spark sql sql query show Explanation For our understanding I ve added extra spaces before and after to Google word By using TRIM function we can able to remove the extra spaces before and after to the word By using RTRIM function we can able to remove extra spaces right side after the word By using LTRIM function we can able to remove extra spaces left side before the word In below output we can easily relate the each section with function and its length function Output I SUBSTRING REPLACE POSITION functions These functions can be applied on String datatype columns Lets explore each of these functions SUBSTRING To extract the given range substring from column REPLACE To replace the column existing data with given new data POSITION To give the exact the position index of the given string in columns data I SUBSTRING function print I Extract IIIT from IIIT Banglore sql query SELECT IIIT Banglore AS FullColumn SUBSTRING IIIT Banglore AS SubstringColumn spark sql sql query show Explanation By using SUBSTRING function we can able to print substring value of any given columns data Parameters are column name starting position number of characters has to be printed If we observe SUBSTRING IIIT Banglore parameters are IIIT Banglore column name or column data is the starting position of string from left side is the number of characters has to be printed from starting position i e So now totally characters will be printed from left side starting postion i e IIIT Output I REPLACE function print I Replace all IIIT to IIIT B sql query SELECT ID Name University REPLACE UNIVERSITY IIIT IIIT B AS Replaced FROM Student Table LIMIT spark sql sql query show Explanation By using REPLACE function we can able to replace the data in given column with given data Here we have used UNIVERSITY column name and IIIT is the existing data Now we are replacing IIIT with IIIT B and alias name for new column alias names can be any thing as per our choice is Replaced Output I POSITION function print I Print symbol position in Email column sql query SELECT ID Name Email POSITION IN Email AS PositionColumn FROM Student Table LIMIT spark sql sql query show Explanation We want to know the position of symbol in the POSITION column for each record Here we have used POSITION IN Email as a syntax to get the positions of given string is the string we are looking and Email is the column name we are searching for symbol As an output we will get for all the records because in all the records we could see in th index Output J Aggregation functions For now lets focus on few aggregated functions which are not required to apply Group by having clauses In next section we will explore more on Aggregation functions with Group by having clauses Lets explore below functions one by one Note These functions will be used in advanced analysis with window functions Group by statements and many more that we are going to explore in the upcoming sections Below few examples are very basic and outputs will be on top of entire table level but not for any specific column level aggregation For column level aggregations we will group by statements in coming sections COUNT Will print the total record count for given scenario MAX Will print the maximum value in column for given scenario MIN Will print the minimum value in column for given scenario SUM Will print the summation value in column for given scenario AVG Will print the average value in column for given scenario J COUNT function print J Print total number of records in the Student Table table sql query SELECT COUNT FROM Student Table spark sql sql query show Explanation COUNT function will print the total number of records available in the table Output J MAX function print J a Print Maximum value in Salary sql query SELECT MAX Salary AS MAX Salary FROM Student Table spark sql sql query show print J b Check Print Table Based on Salary Descending order sql query SELECT FROM Student Table ORDER BY Salary DESC LIMIT spark sql sql query show Explanation In J a section we have applied the MAX function on Salary column In the output we could see the maximum salary from the table For validation purpose in J b section we are sorting the Salary column in descending order so that maximum salary record will come first This kind of validation required in analytics field for all the concepts based on scenario we need to check with other approach to test the results are coming proper or not Output J MIN function print J a Print Minimum value in Salary sql query SELECT MIN Salary AS MIN Salary FROM Student Table spark sql sql query show print J b Validation Print Table Based on Salary Ascending order sql query SELECT FROM Student Table ORDER BY Salary LIMIT spark sql sql query show Explanation In J a section we have applied the MIN function on Salary column In the output we could see the minimum salary from the table For validation purpose in J b section we are sorting the Salary column in ascending order so that minimum salary record will come first Output J SUM AVG functions print J a SUM AVG functions sql query SELECT SUM Salary AS SumSalary AVG Salary AS AverageSalary FROM Student Table spark sql sql query show print J b Validation for Average value based on sum value SUM Total records sql query SELECT as validation spark sql sql query show Explanation By using SUM function we can able to add all salaries from the table By using AVG function we can get the average value of salaries For validation purpose we have checked the formula Average Sum Total records and both outputs are matching for Average value Output K GROUP BY HAVING clauses Till now we have used aggregations on table level If we want to use or more column level aggregations we will be using GROUP BY HAVING clauses Lets explore few examples on this topic More option these GROUP BY HAVING clauses can be used all together GROUP BY To apply aggregations on column s level HAVING To filter the outputs based on specific aggregated conditions K GROUP BY Example print K Print number of Students working in each company sql query SELECT Company count TotalStudents PerCompany FROM Student Table GROUP BY Company spark sql sql query show Explanation We know that count can be used to get the total records from table But when we use count with GROUP BY clause it will group the outputs into clusters of given group by column data distinct values and give the output accordingly Ex In this example we have used GROUP BY Company code so that outputs will be grouped into company distinct values and because of count TotalStudents PerCompany aggregated total students counts will be printed to each company For example in Google company students got placed Output K GROUP BY Example print K Print Total salary of Students based on company Note Round function used sql query SELECT Company ROUND SUM Salary TotalSalary PerCompany FROM Student Table GROUP BY Company spark sql sql query show Explanation This is similar example for above one Here we want to know total salary offered by each company for all the students Here we have applied SUM function on Salary and we are grouping the output on Company column So that in the output we will get total salary offered by each company Output K GROUP BY Having print K Print list of companies which recruites more than students sql query SELECT Company COUNT AS Company Morethan Stu FROM Student Table GROUP BY Company HAVING Company Morethan Stu gt spark sql sql query show Explanation If we see the K example we have totally companies w r t count of students got selected Now by using Having clause we are filtering the aggregated results based on some condition In the output we could see that those companies which hired morethan students and Amazon company hired only students and it is not there in the output Note We cannot use filter the aggregated results with WHERE clause that is the reason we are using HAVING clause We can use WHERE clause before GROUP BY to filter the data Output L Sub Queries Subqueries can be used inside of the other SQL queries We mainly see subqueries in SELECT or WHERE clauses of SQL queries We mainly use subqueries to restrict the output of outer queries based on some condition Subquires and Joins are serve the same purpose of combining data from multiple tables but joins will be used to combine tables based on matching column from both the tables where as subqueries will restrict the records based on single value or list of values Subqueries will be enclosed with parenthesis L Subquery Example print L Print Student details with uniersity having PlayGround sql query SELECT FROM Student Table WHERE University IN SELECT University FROM University Table WHERE PlayGround YES spark sql sql query show print L Cross verify which Universities have Playground sql query SELECT University FROM University Table WHERE PlayGround YES spark sql sql query show Explanation Lets say we want to print all the students details where university should have PlayGround By using where clause we can simply do this but Student Table table doesn t have PlayGround details In this scenario we are filtering the records of Student Table by using subquery in where clause We have created subquery which only returns University names having PlayGround Now outer query results restricted to subquery output values If we use subqueries in where clause we are simply passing or more values to filter the data just like we did in basic where clause examples Output M Correlated sub queries Correlated subqueries are similar to subqueries but in Correlated subqueries will be executed row by row i e each subquery will be executed once for each row of the outer query This will take more time to provide the output But in normal subqueries output of subquery will be generated first and send the values to outer query Here subquery will not be executed morethan once M Correlated sub query Example print M Print Student details where university is in University table sql query SELECT ID Name Email Location UniversityFROM Student Table outer queryWHERE EXISTS SELECT University FROM University Table inner query WHERE inner query University outer query University spark sql sql query show print M Cross verify which Universities are in University Table sql query SELECT University FROM University Table spark sql sql query show Explanation Here in subquery we have used outer query matching filter to get the university details from University Table Due to this for each row of outerquery the innerquery will be evaluated to check the given condition This concept will take more time than normal subqueries Insted of using this concept we need to check if there is any other alternative for this and use that concept Output N Case statement By using CASE statements we can able to create a new column with multiple if else conditions by returning a value to the given conditions It is similar to if elif else statements in any programming language In SQL we apply these conditional flows by checking each row and assigning proper value in new column Lets see an example to understand the concept N Case Statement Example print N Print fullform Gender details based on given shortform sql query SELECT ID Name Gender CASE WHEN Gender M THEN Male WHEN Gender F THEN Female ELSE Other END Gender FullForm FROM Student Table spark sql sql query show Explanation By using CASE statement we are creating Gender FullForm column based on Gender existing column Here we have used WHEN THEN as multiple if statements and ELSE as not matching values in WHEN THENstatements and END finishing followed by new column name we want to create This concept will be very useful in real time data analysis to create new columns based on existing data conditions Output O Joins INNER LEFT RIGHT FULL CROSS When we want club columns from multiple tables based on matching criteria we will use Joins in SQL In most of time as a Data Analyst we spent time on connecting to multiple tables and bring all required columns in single table Here JOINS will help us doing the same Joins concept used for clubbing multiple tables horizontally combining columns and UNION concepts used to club multiple tables vertically combining rows INNER JOIN Returns records based on matching values in both tables LEFT JOIN Returns all records from left table matching records from right table RIGHT JOIN Returns all records from right table matching records from left table FULL JOIN Returns all records from left table right table CROSS JOIN Returns Cartesian product of rows from both tables O Joins concept overview with Example print O List Distinct Universities from Student Table sql query SELECT DISTINCT University FROM Student Table spark sql sql query show print O List Distinct Universities from University Table sql query SELECT DISTINCT University FROM University Table spark sql sql query show print O List matching Universities from both tables Student Table University Table sql query SELECT DISTINCT A University FROM Student Table A INNER JOIN University Table BON A University B University spark sql sql query show Explanation In this Joins section we will be using Student Table University Table to apply joins concepts for University column Before that if we observe below colorful diagram we will easily understand the JOINS easily In both tables IIT IIIT IISC universities are matching NIT VIT only available in Student Table here we have matching universities aswell MIT JNTU only available in University Table here we have matching universities aswell Lets explore different types of JOINS with examples Output O INNER JOIN print O Inner join Query with University column sql query SELECT A ID A Name A University AS University A B University AS University B B PlayGround B Total Students FROM Student Table A INNER JOIN University Table BON A University B University spark sql sql query show Explanation Here we have used new keywords called INNER JOIN after the FROM table statement This means we are applying Inner join concept between Student Table alias A and University Table alias B In the output we will get only matching University values between these tables i e whichever row having IIT IIIT IISC will be printed in the output Output O LEFT JOIN print O LEFT join Query with University column sql query SELECT A ID A Name A Company A Salary A University AS University A B University AS University B B PlayGround B Total Students FROM Student Table A LEFT JOIN University Table BON A University B University spark sql sql query show Explanation Here LEFT JOIN can also called as LEFT OUTER JOIN With Left join concept all the left table records will be printed more than existing records also possible when multiple matches found in right or second table matching records from right table will have proper values and non matching records from right table will have null values In the diagram University A column have all the values available from Student Table in University B column we have proper values for matching values IIT IIIT IISC and null will be applicable for non matching values NIT VIT If we select other columns from right table those column values will also be represented with null values for non matching column values which we used in join condition Output O RIGHT JOIN print O RIGHT join Query with University column sql query SELECT A ID A Name A Company A Salary A University AS University A B University AS University B B PlayGround B Total Students FROM Student Table A RIGHT JOIN University Table BON A University B University spark sql sql query show Explanation Here RIGHT JOIN can also called as RIGHT OUTER JOIN With RIGHT join concept all the right table records will be printed more than existing records also possible when multiple matches found in left or other table matching records from left table will have proper values and non matching records from left table will have null values In the diagram University B column have all the values available from University Table in University A column we have proper values for matching values IIT IIIT IISC and null will be applicable for non matching values MIT JNTU If we select other columns from left table those column values will also be represented with null values for non matching column values which we used in join condition If we observe we have more records in output even though only records in University Table this is because one value in right table have multiple matches in left table Output O FULL OUTER JOIN print O FULL OUTER join Query with University column sql query SELECT A ID A Name A Company A Salary A University AS University A B University AS University B B PlayGround B Total Students FROM Student Table A FULL OUTER JOIN University Table BON A University B University spark sql sql query show Explanation Here FULL JOIN can also called as FULL OUTER JOIN This will return all the values from left right tables For matching values in join condition will be assigned proper values and for non matching values null will be assigned If we observe the diagram FULL JOIN is nothing but LEFT JOIN RIGHT JOIN In University A column we can see NIT VIT values and for these values in University B or right table null will be assigned because of non matching criteria Similar way in University B column we can see MIT JNTU values and for these values in University A or left table null will be assigned because of non matching criteria Output O CROSS JOIN print O Example for Cross Join Query print nCount of Student Table student dfps count print nCount of University Table university dfps count print nCount Student Table X Count University Table student dfps count university dfps count sql query SELECT A ID A Name A Company A Salary A University AS University A B University AS University B B PlayGround B Total Students FROM Student Table A CROSS JOIN University Table BORDER BY A ID B University spark sql sql query show Explanation Lets say we have tables and we want to get the each row of first table with each row of second table combination CROSS JOIN will help us on this We will get Cartesian product of rows from each table we used in join query Here we wont use any matching criteria In below Student Table have records University Table have records Now when we apply CROSS JOIN we will get X records in output If we observe the below output for one ID let say from left table we have records which contains all universities IIIT IISC IIT JNTU MIT from right table Black box in the diagram have each student details from Student Table in Blue box the entire University Table will be assigned Output P Union Union all Except Joins concept used for clubbing multiple tables horizontally combining columns and UNION concepts used to club multiple tables vertically combining rows To apply UNION UNION ALL EXCEPT concepts we need make sure number of columns order of the columns should be similar in both the tables UNION Records will be clubbed and only distinct values will be printed UNION ALL Records will be clubbed and all values will be printed can have duplicates EXCEPT Acts as Minus and return the extra values in first table compared to second table P UNION print P UNION Example sql query SELECT University FROM Student TableUNIONSELECT University FROM University Table spark sql sql query show Explanation To club multiple tables vertically combining records we use UNION statements Here duplication is not allowed Same number of columns and order also we need to maintain same for tables in UNION concepts In this example all the University values from Student Table NIT IIT IIIT IISC VIT and values from University Table MIT JNTU combined in the output without duplication Output P UNION ALL print P UNION ALL Example sql query SELECT University FROM Student TableUNION ALLSELECT University FROM University TableORDER BY University spark sql sql query show Explanation To club multiple tables vertically combining records we use UNION ALL statements Here duplication is allowed Same number of columns and order also we need to maintain same for tables in UNION ALL concepts In this example all the University values from Student Table NIT IIT IIIT IISC VIT and values from University Table MIT JNTU combined in the output with duplication Output P EXCEPT print P EXCEPT Example sql query SELECT University FROM Student TableEXCEPTSELECT University FROM University Table spark sql sql query show Explanation If we want to subtract the values from first select statement to second one we use this EXCEPT concept Here duplication is not allowed This will only give the extra records from first select statement compared to second select statement Output Q Window functions Till now we seen Group By statements to apply aggregate functions to return single value for that group By using Window functions we can able to partition the relevant records and we can do lot of customization within that partition such that we can achieve sorting the values within partition or assign some rank values by sorting the values or even we can able to get the running aggregation values for partition Parition is nothing but grouping values based on some column s In real time data analysis Window functions can play very important role to get the more insights about data We can apply these window functions for partitions or for entire table level also By using window functions we are going to create a new column which have outcome of the window function In all window functions we will be using OVER clause where we will be mentioning based on which column s table should be partitioned i e Group By and which columns has to be in sorting order i e Order By With these details new column will be created We have couple of windows functions we are going to cover in this section ROW NUMBER Assign a sequential value starts with for each partition values RANK Assign the sequential value if similar values found then rank will be same and for coming value rank will be skipped to those many similar values from original rank value DENSE RANK Assign the sequential value if similar values found then rank will be same and for coming value rank will not be skipped Continuous rank will be applicable NTILE Distribute the entire table sorted records into specific number of equal groups or buckets LEAD will provide the value leading to given offset number of positions for current row LAG will provide the value lagging to given offset number of positions for current row Running aggregation functions in window functions COUNT SUM AVG MIN MAX Returning aggregation value till that row from starting of partition Q ROW NUMBER Example without Partition print Q Row number based ID without Partition sql query SELECT ID Name Gender Salary Location Company ROW NUMBER OVER ORDER BY ID AS RowNumber by IDFROM Student Table spark sql sql query show Explanation ROW NUMBER is mainly used to create a new column with sequential number starting with for entire table level or for each partition In this example we are covering for entire table level without any partition next example we will cover for partition level With ROW NUMBER function ORDER BY clause is mandatory to use In this example ROW NUMBER OVER ORDER BY ID AS RowNumber by ID after the ROW NUMBER function we have used OVER clause where we have mentioned how the output format should be GROUP BY ORDER BY For new column we are renaming with ALIAS keyword followed by new column name RowNumber by ID Output Q ROW NUMBER Example with Partition print Q Row number PARTITION BY Location ORDER BY Company sql query SELECT ID Name Gender Salary Company Location ROW NUMBER OVER PARTITION BY Location ORDER BY Company AS RowNumber Location CompanyFROM Student Table spark sql sql query show Explanation Here ROW NUMBER OVER PARTITION BY Location ORDER BY Company AS RowNumber Location Company we have used PARTITION BY on top of Location column so that output will be grouped based on Location column and ORDER BY based on Company so that in each group of Location all available records will be sorted according to Company in ascending order default order type Now within each Partition we can able to see sequence numbers starting from This sequence will be reset to again for next partition In the output we could see that in Location column all similar values grouped together and in Company column all the available companies in that location will be sorted ascending order Now the new column RowNumber Location Company has been created with sequential number for each Location No duplicates found in within same group Output Q RANK print Q Rank function with PARTITION BY Company ORDER BY Salary sql query SELECT ID Name Gender Location Company Salary RANK OVER PARTITION BY Company ORDER BY Salary DESC AS Rank Salary CompanyFROM Student Table spark sql sql query show Explanation By using RANK function we are grouping Partition the records based on Company column and sorting ORDER BY the Salary in descending order and creating a new Rank Salary Company column In new column if we observe for Microsoft company Salary details are in descending order and forID we have same salaries highlighted in screenshot If we have similar values in RANK function it will assign the same rank for similar records but coming ID rank has been assigned as Because number of similar values and rank for those similar values so now RANK function will add these numbers and assign the rank for up coming records Same we can observe for ID rank as RANK function will assign the same sequence number for similar values and skip the sequence number till general sequence number without skipping and without repetition and assign that number i e whatever we get after applying ROW NUMBER we will get the same if we have similar record values Output Q DENSE RANK print Q DENSE RANK function with PARTITION BY Company ORDER BY Salary sql query SELECT ID Name Gender Location Company Salary DENSE RANK OVER PARTITION BY Company ORDER BY Salary DESC AS DENSE Rank Salary CompanyFROM Student Table spark sql sql query show Explanation By using DENSE RANK function we are grouping Partition the records based on Company column and sorting ORDER BY the Salary in descending order and creating a new DENSE Rank Salary Company column In new column if we observe for Microsoft company Salary details are in descending order and forID we have same salaries highlighted in screenshot If we have similar values in DENSE RANK function it will assign the same rank for similar records but for coming ID DENSE RANK has been assigned as Here in DENSE RANK sequence number will not get skipped and after all matching records completed without breaking sequence will be continued Same we can observe for ID DENSE RANK DENSE RANK function will assign the same sequence number for similar values and won t skip the sequence number for upcoming records and sequence will be continued Output Q ROW NUMBER RANK DENSE RANK in single output print Q ROW NUMBER RANK DENSE RANK in single output sql query SELECT ID Name Gender Location Company Salary ROW NUMBER OVER PARTITION BY Company ORDER BY Salary DESC AS ROW NUMBER RANK OVER PARTITION BY Company ORDER BY Salary DESC AS RANK DENSE RANK OVER PARTITION BY Company ORDER BY Salary DESC AS DENSE RANKFROM Student Table spark sql sql query show Explanation Just for comparison purpose I ve added ROW NUMBER RANK DENSE RANK functions in single query By seeing this example we can easily understand that ROW NUMBER is sequence number without any breaks or duplicates RANK will have duplicates when data matches and skip the upcoming records ranking DENSE RANK will have duplicates when data matches and wont skip the upcoming records ranking Output Q NTILE print Q Distribute ID s into NTILE equal buckets sql query SELECT ID Location NTILE OVER ORDER BY ID AS NTILE on IDFROM Student Table spark sql sql query show Explanation To apply NTILE function at least one column should have sorted data Lets say if we want to divide entire table rows into some equal buckets we can use this function Simply that we are creating a new column by assigning bucket number for each row We are mentioning in NTILE function itself how many buckets we are going to place for all records Based on this number record count of table will divide and group those many number of records to each bucket Sometimes for last bucket we may see less records than other buckets because of record count dividable by given number of buckets might not give always zero as a reminder Here we have total of records and number of buckets are with this we will definately get only records in last bucket Output Q LEAD print Q Lead function on Salary column by offset sql query SELECT ID Location Company Salary LEAD Salary OVER ORDER BY ID AS Lead SalaryFROM Student Table spark sql sql query show Explanation LEAD function is used to create a new column based on existing column values by leading the given number of offset records to the existing column values Here offset number is important because those many number of records will be skipped first and assigning values after the offset position Similar way in new column end of the records will have offset number to null values because there will not be any values to be assigned Simply after the offset position entire column value will be copied and at the end null will be replaced Here LEAD Salary the column we are going to apply this function is Salary and offset is so first record data skipped wont be copied and rest of the values will be copied to new column In new column records have null values Output Q LAG print Q Lag function on Salary column by offset sql query SELECT ID Location Company Salary LAG Salary OVER ORDER BY ID AS Lag SalaryFROM Student Table spark sql sql query show Explanation LAG function is used to create a new column based on existing column values by lagging the given number of offset records to the existing column values Here offset number is important because those many number of records will be replaced as null in new column and then rest of the data will be copied into new column after that offset Simply with given offset number of records in new column will be replaced with null then data will be copied to new column from after offset position Here LAG Salary the column we are going to apply this function is Salary and offset is so first records in new column will be replaced as null and then entire data will be copied to new column But for last given offset number of records in Salary values wont be copied to new column because there are no records in entire table and those many offset number of values will be missed to copy Output Q Running aggregation functions in window functions COUNT SUM AVG MIN MAX print Q Running Total example on Salary Column partition by Company sql query SELECT ID Location Company Salary SUM ROUND SALARY OVER PARTITION BY COMPANY ORDER BY SALARY AS Running TotalFROM Student Table spark sql sql query show Explanation In running aggregation functions we can use all aggregation functions we already used in earlier sections Here in this example we are using SUM for running aggregation i e we call it as running total This will give the sum of given column till current row It will calculate for every record in given partition Value will get reset to new partition as first record value in that partition In this example we have used Salary column for running total with company as partition Lets say for Google company first row will be same as existing value in second row value will be assigned as sum of first row second row Similarly for rd row value be calculated as sum of st nd rd rows values For new partition Microsoft the process will start again in same way Output Conclusion I hope you have learned SQL concepts with simple examples Happy Learning |
2022-10-24 20:08:47 |
| 海外TECH |
DEV Community |
How does the internet work |
https://dev.to/edith_favour/how-does-the-internet-work-1hh7
|
How does the internet workEver wondered what happens when you load a webpage using a URL How does your favorite webpage load up in your browser within a fraction of a second after you search How is this page loaded and you are able to read about how the internet works The web sometimes referred to as the world wide web www is a collection of information that is accessed via the internet The internet is a global network of networks i e the infrastructure on which the web runs Think of the internet as a road that connects towns and cities The web contains the things you see on the road like houses and shops The web uses a client server based architecture The client in this context is a browser that sends a request to the server for information The server receives the request sent by the client processes the request and sends client data based on the request received The data and the process are stored on the web server The web browser and server communicate using HTTP HTTPS a web communication protocol How exactly does it work When a user clicks or types in a URL or domain name in a browser a request is sent to your ISP s internet service provider DNS which checks and maps the domain name to its IP address If the IP address is not found the ISP will query an authoritative DNS for the IP address This will send the IP address of the destination server to the browser The browser sends an HTTP HTTPS request message to the server asking it to send a copy of the website to the client This message and all other data sent between the client and the server is sent across your internet connection using TCP IP The data is wrapped with specific information including the server s IP address for your request The server reads the request and if the web page exists or is available pulls information from the server and starts sending the website files to the browser as a series of tiny chunks or packets The browser assembles the packet into a complete web page understandable by humans and displays it on the user s browser Although it may seem complicated the way websites work is pretty straightforward |
2022-10-24 20:02:36 |
| ニュース |
BBC News - Home |
Rishi Sunak: Your questions answered |
https://www.bbc.co.uk/news/uk-politics-63380804?at_medium=RSS&at_campaign=KARANGA
|
minister |
2022-10-24 20:15:10 |
| ニュース |
BBC News - Home |
New UK prime minister: What comes next for Rishi Sunak? |
https://www.bbc.co.uk/news/uk-politics-62596329?at_medium=RSS&at_campaign=KARANGA
|
downing |
2022-10-24 20:42:18 |
| ニュース |
BBC News - Home |
Rugby League World Cup: Tonga 32-6 Wales - Tonga cruise to victory after early scare |
https://www.bbc.co.uk/sport/rugby-league/63378130?at_medium=RSS&at_campaign=KARANGA
|
Rugby League World Cup Tonga Wales Tonga cruise to victory after early scareTonga put one foot in the Rugby League World Cup quarter finals but are made to work hard before beating a gutsy Wales side |
2022-10-24 20:25:44 |
| ニュース |
BBC News - Home |
Rugby League World Cup: Tonga 'sensational' try ruled out against Wales |
https://www.bbc.co.uk/sport/av/rugby-league/63380983?at_medium=RSS&at_campaign=KARANGA
|
Rugby League World Cup Tonga x sensational x try ruled out against WalesWatch the moment Tonga produce a sensational piece of play against Wales only for video referee to rule the try out at the Rugby League World Cup |
2022-10-24 20:42:38 |
| ビジネス |
ダイヤモンド・オンライン - 新着記事 |
富裕層が熱烈支持する「航空機投資」成功の秘訣と3つの注意点 - 円安・金利高・インフレに勝つ！最強版 富裕層の節税＆資産防衛術 |
https://diamond.jp/articles/-/311269
|
超富裕層 |
2022-10-25 05:25:00 |
| ビジネス |
ダイヤモンド・オンライン - 新着記事 |
篠原ともえさんに会心のインタビューができた理由、いい会話を生む5つのコツ - 日本人に足りない「伝え方＆表現」スキル |
https://diamond.jp/articles/-/311495
|
篠原ともえさんに会心のインタビューができた理由、いい会話を生むつのコツ日本人に足りない「伝え方表現」スキル筆者はこれまで米倉涼子さんや山下智久さんなど芸能人をはじめ、井上尚弥さんや葛西紀明さんなどアスリートなどエンタメやスポーツ、ビジネス界の第一線で活躍する人以上のインタビュー取材をしてきました。 |
2022-10-25 05:20:00 |
| ビジネス |
ダイヤモンド・オンライン - 新着記事 |
「早慶ブランド通用しない」関西の高校生保護者が語る学校選びのリアル、公立？中高一貫？大学付属？ - 超お得！【関西】中高一貫・高校＆大学最新序列 |
https://diamond.jp/articles/-/311641
|
中高一貫 |
2022-10-25 05:15:00 |
| ビジネス |
ダイヤモンド・オンライン - 新着記事 |
慶應「評議員継続」ランキング【76人】サントリー、三井不の首脳ら23人が在任20年超！ - 最強学閥「慶應三田会」 人脈・金・序列 |
https://diamond.jp/articles/-/311624
|
三井不動産 |
2022-10-25 05:10:00 |
| ビジネス |
ダイヤモンド・オンライン - 新着記事 |
【学習動画】海洋国家同士と大陸国家同士、それぞれ対立する理由は？はじめての地政学 - Udemy発！学びの動画 |
https://diamond.jp/articles/-/311156
|
udemy |
2022-10-25 05:05:00 |
| ビジネス |
電通報 | 広告業界動向とマーケティングのコラム・ニュース |
「ＥＮＥＯＳ 新車のサブスク」から見る、オンラインとオフラインを統合したCXの実現 |
https://dentsu-ho.com/articles/8354
|
eneos |
2022-10-25 06:00:00 |
| 北海道 |
北海道新聞 |
＜社説＞山際担当相辞任 首相の任命責任は重い |
https://www.hokkaido-np.co.jp/article/750269/
|
世界平和統一家庭連合 |
2022-10-25 05:01:00 |
| ビジネス |
東洋経済オンライン |
｢日本人は無宗教｣と信じる人が気づいてない真実 自然宗教､神道の国教化､心学…特有の3つの事情 | 不安な時代、不機嫌な人々 | 東洋経済オンライン |
https://toyokeizai.net/articles/-/627356?utm_source=rss&utm_medium=http&utm_campaign=link_back
|
世界平和統一家庭連合 |
2022-10-25 05:30:00 |
コメント
コメントを投稿