SQL Analytic Functions

 

Over past year I have really improved my SQL skills

The analytic functions are pretty power full

https://stackoverflow.com/questions/5736820/sql-how-to-select-earliest-row

Numpy for Python

•  python
• >>> import numpy as np
• >>> foo = [[1,5,9]]
• >>> print (foo)
• [[1, 5, 9]]
• >>> print (np.average(foo))
• 5.0
• >>> a = np.array ([[1,5,9]])
• >>> print (a)
• [[1 5 9]]
• >>> a
• array([[1, 5, 9]])
• >>> a.shape
• (1, 3)
• >>> a.size
• 3
• >>> a.ndim
• >>> a.itemsize
• 8
• >>> a.dtype
• dtype(‘int64’)
• >>> b = np.array ([[1,2,3], [4,5,6]])
• >>> b
• array([[1, 2, 3],
•        [4, 5, 6]])
• >>> b.shape
• (2, 3)
• >>> b.ndim
• 2

 

http://www.numpy.org/

 

https://docs.scipy.org/doc/numpy-dev/user/quickstart.html

 

Python

condo update anaconda

spyder perl tutorial https://www.youtube.com/watch?v=J5GevIHNctM

http://www.numpy.org/

https://www.scipy.org

console : print “hello”

editor: run by select f9

inspector : debugging

active state

python tutorial https://pythonprogramming.net/introduction-to-python-programming/

https://www.youtube.com/watch?list=PLQVvvaa0QuDe8XSftW-RAxdo6OmaeL85M&time_continue=163&v=IX6mc9l6tY4

https://www.youtube.com/watch?v=5aP9Bl9hcqI

https://docs.cs50.net/2016/fall/notes/8/week8.html

https://www.cs.cornell.edu/courses/cs1110/2017fa/lectures/index.php python class

http://stanfordpython.com/#lecture

http://arc-ts.umich.edu/mini-course-introduction-to-python-sept-11-14/ python

https://stanford.edu/~schmit/cme193/ python

sql case statement

 

WITH
dt (idx, FISCAL_YR_VAL, FISCAL_YY) as
(
select
ROW_NUMBER() over (order by years.yr) as idx, yr as FISCAL_YR_VAL , yr-2000 as FISCAL_YY
from (select top 5 ROW_NUMBER() over(order by a.name) + 2014 as yr from sys.all_objects a) years
where years.yr < 2017 +1

) ,

dx (idx, FISCAL_YR_VAL, FISCAL_YY) as
(
select
ROW_NUMBER() over (order by years.yr) as idx, yr as FISCAL_YR_VAL , yr-2000 as FISCAL_YY
from (select top 5 ROW_NUMBER() over(order by a.name) + 2014 as yr from sys.all_objects a) years
where years.yr < 2017 +1

)

Select
* ,
case
when idx > 1 then 3
else idx
end evnt_typ
from dt ;

SQL With

Using mssql:

pulling data out of air from the system tables:

 

select top 5 ROW_NUMBER() over(order by a.name) + 2014 as yr from sys.all_objects a

above query gives you 5 rows with column yr with values 2015-2019

WITH ds (idx, FISCAL_YR_VAL, FISCAL_YY) as
(
select
ROW_NUMBER() over (order by years.yr) as idx, yr as FISCAL_YR_VAL , yr-2000 as FISCAL_YY
from (select top 5 ROW_NUMBER() over(order by a.name) + 2014 as yr from sys.all_objects a) years
where years.yr < 2017 +1
union
select ROW_NUMBER() over (order by years.yr) as idx, yr+8 as FISCAL_YR_VAL, yr-2000 + 8 as FISCAL_YY
from (select top 5 ROW_NUMBER() over(order by a.name) + 2014 as yr from sys.all_objects a) years
where years.yr < 2017 +1
)
Select * from ds ;

 

 

Create more data

 

select CONCAT(‘ABC’, CONVERT(varchar(10), (ROW_NUMBER() over (order by years.yr)) )) as code,
CASE WHEN yr % 2 = 0 THEN ‘Apple’ WHEN yr % 2 = 1 THEN ‘Android’ END as typ
, yr as ItemCount , years.yr + 14 as amt
from (select top 5 ROW_NUMBER() over(order by a.name) as yr from sys.all_objects a) years
where years.yr < 2017 +1
union
select CONCAT(‘ABC’, CONVERT(varchar(10), (ROW_NUMBER() over (order by years.yr)) )) as code
,
CASE WHEN (yr+7) % 2 = 0 THEN ‘Android’ WHEN (yr+7) % 2 = 1 THEN ‘Apple’ END as typ
, yr + 8 as ItemCount , years.yr + 14 as amt
from (select top 5 ROW_NUMBER() over(order by a.name) as yr from sys.all_objects a) years
where years.yr < 2017 +1

The following grouping

To sum up  the totals per code

SELECT code,SUM(ItemCount) AS ItemCount,Typ,SUM(amt) AS Amount
FROM
ds
GROUP BY code, typ;

To sum up  the total amt of bill , but  give total number of items per code

SELECT
Code, SUM(ItemCount) ItemCount,Typ,
(SELECT SUM(Amt) FROM ds) Amount
FROM
ds
GROUP BY Code, Typ;

Health Vault

http://healthvaultjavalib.codeplex.com/

this is a library that can be used to write applications in Java that use the HealthVault servers. It does not implement all the functionality that is present in the HealthVault .NET API libraries, but does provide a framework in which that support can be implemented.

http://msdn.microsoft.com/en-us/healthvault/bb802509.aspx#_Installing_the_SDK

“With HealthVault, users control their own health records, so they can share their health information with family, friends, and health care professionals, and have access to trustworthy”

 

 

 

http://blogs.msdn.com/b/healthvault/archive/2013/02/18/a-healthvault-application-development-primer-using-asp-net-mvc.aspx

Healthvault login page can be invoked from here:
https://www.healthvault.com/us/en

Healthvault Config URL –> https://config.healthvault-ppe.com/default.aspx

https://config.healthvault-ppe.com/CreateNewApp.aspx

From http://msdn.microsoft.com/en-us/library/dn539122.aspx#connectivityModels under Direct Messaging:

“Applications that use Direct Messaging integration to send CCDAs to HealthVault for Meaningful Use purposes, must register an application in the Application Configuration Center, enable Meaningful Use on theMethods tab, and associate their sending Direct Messaging domain with the application ID on the Meaningful Use tab. When the application is ready to be configured in the HealthVault production environment, contact hvbd@microsoft.com to start the process.”

http://msdn.microsoft.com/en-us/library/dn539122.aspx#vdtReport

http://msdn.microsoft.com/en-us/library/dn539122.aspx#_Key_CCDA_fields

http://mangstacular.blogspot.com/2010/03/creating-connect-request-in-healthvault.html

https://developer.healthvault.com/default.aspx

API : http://developer.healthvault.com/pages/sdk/docs/index.html

Methods: https://developer.healthvault.com/pages/methods/methods.aspx

http://developer.healthvault.com/pages/sdk/docs/GetMeaningfulUseVDTReport.html
http://developer.healthvault.com/pages/sdk/docs/GetServiceDefinition.html
http://developer.healthvault.com/pages/sdk/docs/CreateAuthenticatedSessionToken.html

https://platform.healthvault-ppe.com/platform/XSD/method-getmeaningfulusetimelyaccessreport.xsd

MU

meaningful-use-101

Meaningful use of health information technology is an umbrella term for rules and regulations that hospitals and physicians must meet to qualify for federal incentive funding under the American Recovery and Reinvestment Act of 2009

 

HL7 Implementation Guide for CDA release 2

 

To receive an EHR incentive payment, providers have to show that they are “meaningfully using” their certified EHR technology by meeting certain measurement thresholds that range from recording patient information as structured data to exchanging summary care records.

http://www.cms.gov/Regulations-and-Guidance/Legislation/EHRIncentivePrograms/Meaningful_Use.html

http://www.dhhr.wv.gov/oeps/documents/2013-symposium/Symposium-MeaningfulUse.pdf

Eligible Professionals (EP)

Clinical Summary

Transition of Care

http://www.healthstory.com/pdf/HealthStoryMeaningfulUse.pdf

http://www.healthstory.com/standards/standards.htm

http://www.himss.org/files/HIMSSorg/content/files/Code%20101_Clinical%20summaries%20and%20meaningful%20use%20A%20primer_Artz_JHIM_W2011.pdf

http://blog.agilehealthservices.com/2013/05/landscape-of-meaningful-use-stage-2.html

http://socialhealthinsights.com/2013/08/what-the-hqmf-ecqms/

 

 

 

Nodejs

http://www.joezimjs.com/javascript/node-js-for-true-beginners/

 

Casandra LInks

cloud times web site why nosql ?

Big Data – Data Never Sleeps

The Casandra paper

The Casandra RefCard

Casandra  roots traced back to facebook

Casandra api example on dumping data from it

Few words on unstructured database blog

Cassandra winning the NoSql race 2009

Cassandra quick tour

Cassandra getting started

WTF is a supercolumn and more

Should the terminology change

Replication in Cassandra and HBase blog

client choices in Cassandra clientdrivers

client cassandra  api

spring data Casandra api

GUI for Cassandra

Compare of Cassandra

http://brianoneill.blogspot.com/2012/08/cassandra-apis-laundry-list.html

http://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis

Clones of Big Table on Cassandra

• Written in: Java
• Main point: Best of BigTable and Dynamo
• License: Apache
• Protocol: Thrift & custom binary CQL3
• Tunable trade-offs for distribution and replication (N, R, W)
• Querying by column, range of keys (Requires indices on anything that you want to search on)
• BigTable-like features: columns, column families
• Can be used as a distributed hash-table, with an “SQL-like” language, CQL (but no JOIN!)
• Data can have expiration (set on INSERT)
• Writes can be much faster than reads (when reads are disk-bound)
• Map/reduce possible with Apache Hadoop
• All nodes are similar, as opposed to Hadoop/HBase
• Very good and reliable cross-datacenter replication

Best used: When you write more than you read (logging). If every component of the system must be in Java. (“No one gets fired for choosing Apache’s stuff.”)

For example: Banking, financial industry (though not necessarily for financial transactions, but these industries are much bigger than that.) Writes are faster than reads, so one natural niche is data analysis.

http://www.planetcassandra.org/blog/post/crowd-riff-moves-to-titan-on-cassandra-drops-mongodb

Cassandra vs HBase NoSql battle

Cassandra at twitter

why they moved to Cassandra blog

blog big data overview slides

cassandra myths infoq

cassandra maven

how do i cassandra slides

cassandra under hood

Big Data annd No SQL links

Hadoop Intro By HortonWorks  Linkedin engineer . you tube

The Cloudera video

Hadoop ecosystem  map with Hortonworks

what is hadoop – quick answer

Intro HBase

Hadoop 2

Database design with hadoop

Columnur vs Key value

Talk with Jim Goodnight on analytics and big data Datanami Live

Lars George talk about file locality blog

The Hadoop Distributed File System description

The Yahoo Hadoop tutorial

HDFS and Map Reduce name node overview

Big Data for Dummies

The engineering behind Facebook messages moves to Hadoop 

architects zone on

Facebook under the hood article

IBM Hadoop space

Hadoop realtime at Facebook paper

Map Reduce paper

The NoSql ecosystem description description

NoSql data modeling blog

whatisbigdata blog

NoSQL and MySQL at Craigslist

Jeremy Zawodny on Lessons learned  migation NoSQL

NoSQL is what?