Different Types Of SQL Windows Functions.

Image for post
Image for post
Photo by: Franki | Unsplash.com

Introduction:

A Window Function performs a Data Analysis calculation across a set of table rows that are somehow related to the current row. Address the comparable type of calculation can be done with an aggregate function that gives a single row or grouped by condition (refer to Figure 1).

Window function does not cause rows to become grouped into a single output row. Rows retain their separate identities also able to access more than just the current row of the query result. (refer to Figure 1).

Window Function Syntax:
Window_Function([All] expression)
OVER( [PARTITION BY expression_list] [ORDER BY order_list Row_or_ Range clause]…


Introduction & Overview Types of Window Function in SQL.

Image for post
Image for post
Image by Author, inspired by the Toptal.com

Introduction:

A window function performs a Data Analysis calculation across a set of table rows that are somehow related to the current row. Address the comparable type of calculation can be done with an aggregate function that gives a single row or grouped by condition (refer to Figure 1).

Window function does not cause rows to become grouped into a single output row. Rows retain their separate identities also able to access more than just the current row of the query result. (refer to Figure 1)

Image for post
Image for post
Figure 1 — Difference between — Aggregated and Windows function

The database used to explain below concepts: Postgres database and Dataset: Available at Github Order_Table.csv


List | Dictionary | Set comprehensions

Image for post
Image for post
Photo by: Debby | Unsplash.com

Introduction:

Python is a popular language that allows programmers to write elegant, easy-to-write and read code like plain English. The unique feature of Python is a different type of comprehensions.

In Python, there are three types of comprehensions viz. List, Dictionary and Set.

By the end of this blog, you’ll understand the full power of Python comprehensions and how to easily use its functionality.

  1. LIST COMPREHENSION

List: List is a collection of data surrounded by square brackets and each element are separated by a comma.

List Comprehension: Is also surrounded by square brackets but instead of the list of elements inside it contain expression like for loop &-or followed by if-clauses. …


Part 1: Basic Introduction About Time-Series Forecasting

Image for post
Image for post
Photo by: Kevin Ku | Unsplash.com

Introduction:

Time Series Forecasting is the use of the statistical model to predict future value based on past observation/results. Use any variables tracked and collected over time. For example, annual population, daily stock prices, daily-weekly-quarterly sales, etc.

Time Series Data Characteristics & Objectives:

  • The data completely depend on data and time. Hence, if the order of date or time change leads to a change in the meaning of data.
  • Data collection must be done sequentially and at an equal interval of time.
  • Once data collection is done properly, the next step is to identify patterns with help of a sequence of observations and forecast/predict the future values in Time series. …

Implemented popular techniques using Python

In my last blogs, I explained types of missing values and different ways to handle Continous and Categorical missing values with implementation.

After handle missing values in the dataset, the next step was to handle categorical data. In this blog, I will explain different ways to handle categorical features/columns along with implementation using python.

Image for post
Image for post
Photo by: AltumCode | Unsplash.com

Introduction: All Machine Learning models are some kind of mathematical model that needs numbers to work with. Categorical data have possible values (categories) and it can be in text form. For example, Gender: Male/Female/Others, Ranks: 1st/2nd/3rd, etc.

While working on a data science project after handling the missing value of datasets. The next work is to handle categorical data in datasets before applying any ML models. …


In my last blog Link, I explained different ways to handle Continuous column missing data and its implementation.

In this blog, I will explain how to handle missing values of the Categorical data column in the dataset with implementation using python.

Image for post
Image for post
Image from: 365datascience.com

Discrete/ Categorical Data: discrete data is quantitative data that can be counted and has a finite number of possible values or data which may be divided into groups e.g. days in a week, number of months in a year, sex (Male/Female/Others), Grades (High/Medium/Low), etc.

The dataset used to explain is Titanic (Kaggle dataset):

import pandas as pd
import numpy as np
Data = pd.read_csv("train.csv")
Data.isnull().sum() …


In my last blog Link, I explained missing values and their types.

In this blog, I will explain how to handle missing values for the Continuous data column in the dataset with implementation.

Image for post
Image for post
Photo by: Scott Graham | Unsplash.com

Continous Data: Continuous data is quantitative data that can be measured, it has an infinite number of possible values within a selected range e.g. temperature range, height, weight, etc.

The dataset used to explain is Titanic (Kaggle dataset)

import pandas as pd
import numpy as np
Data = pd.read_csv("train.csv")
Data.isnull().sum()
Image for post
Image for post
Null values present in the dataset
  1. Mean / Median / Mode Imputation:

Assumption: Data is Missing Completely at Random(MCAR).

Description: Replacing NAN values with the most frequent occurrence of variable. …


Image for post
Image for post
Copyright: Abbott logo

Abbott is headquartered in Abbott Park, Illinois, United States, an American multinational healthcare and medical equipment corporation. Business established initially in 1888 to formulate drugs; now they market medical equipment, diagnostics, generic branded drugs, and nutritional goods (Wikipedia Contributors, 2019). Abbott well-known market leader for glucose monitoring devices, blood and plasma screening, remote heart failure monitoring, chronic pain devices, and many others. Dr. David Spindell, Abbott’s vice president of medical and clinical affairs at the Tech Health conference for the Wall Street Journal in San Francisco, said, AI and data analysis help us gain insights into health care. It allows us to build solutions for unmet needs by seeking some main insights. We can’t avoid using machine learning to fine-tune those products (abbott.com, …


Image for post
Image for post
photo by: NEW DATA SERVICES | Unsplash

In the life cycle of the data science project, the data has been collected from various sources like internal databases, 3rd party API’s or by surveys. Data engineers usually take care of adding collected data into databases. But, when data come to data scientists or analysts most of the time data has some missing values or some unacceptable characters. It is found that 80 percent of overall project time has been utilized in data preparation in order to make data ready for analysis.

First, understand why there are missing values.

  • Non-Response: Information not filled by subjects, for example, peoples usually don’t like to reveal their salaries, age, mobile number, etc. …

About

Ganesh Dhasade

Data Scientist | Analyst | Enthusiast | ML Engineer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store