#datetime aggregation techniques

[thedbahub]

Grouping Data by Time Intervals in SQL Server: Hourly and 10-Minute Aggregations

In SQL Server, grouping data by time intervals such as by hour or by 10 minutes requires manipulation of the date and time values so that rows falling within each interval are grouped together. This can be achieved using the DATEPART function for hourly grouping or a combination of DATEPART and arithmetic operations for more granular groupings like every 10 minutes. Here’s how you can do…

[shalu620]

Potential of Python: Your Path to Mastery and Practical Application

Python programming stands as a pillar of modern software development, renowned for its versatility and robust capabilities across diverse industries. Considering the kind support of Learn Python Course in Pune, Whatever your level of experience or reason for switching from another programming language, learning Python gets much more fun.

Whether you're starting from scratch or seeking to enhance your programming prowess, mastering Python offers a gateway to numerous career opportunities. This guide explores effective strategies for learning Python comprehensively and leveraging its capabilities across different applications.

Mastering Python: Your Learning Journey

1. Grasping the Fundamentals

Begin your Python journey by familiarizing yourself with its syntax, basic data types (such as integers, floats, strings), and fundamental operations. Online platforms like Codecademy and Udemy offer structured courses tailored for beginners, providing a solid foundation to build upon.

2. Navigating Control Structures

Dive into Python’s control structures—loops (for, while), conditional statements (if, else), and functions. These elements are essential for writing efficient and organized code, enabling you to tackle complex problems systematically.

3. Exploring Data Handling

Python’s built-in data structures—lists, dictionaries, tuples, and sets—empower you to manage and manipulate data effectively. Mastering these structures equips you with the tools to handle diverse data sets and optimize data-driven workflows.

4. Embracing Object-Oriented Programming

Delve into object-oriented programming (OOP) principles such as classes, inheritance, and polymorphism. Understanding OOP enhances code organization, reusability, and scalability—essential traits for developing robust, maintainable applications.

5. Harnessing Python Libraries

Python’s extensive library ecosystem caters to various domains. Start with foundational libraries like math and datetime, progressing to specialized tools such as pandas for data analysis, matplotlib for visualization, and frameworks like Flask or Django for web development. Enrolling in the Best Python Certification Online can help people realise Python’s full potential and gain a deeper understanding of its complexities.

6. Practical Application and Challenges

Apply your knowledge through coding challenges, small projects, or participation in coding platforms like LeetCode. Hands-on experience not only reinforces learning but also sharpens problem-solving skills crucial for real-world scenarios.

7. Engaging with the Community

Join online communities such as Stack Overflow and GitHub to collaborate, seek advice, and stay updated with industry trends. Engaging with peers and experts fosters a supportive learning environment and exposes you to diverse perspectives and best practices.

Leveraging Python: Applications Across Domains

1. Web Development and Beyond

Utilize Python frameworks like Flask and Django to build scalable web applications. Learn to integrate front-end technologies, manage databases, and deploy robust web solutions tailored to diverse user needs.

2. Data Analysis and Visualization

Python’s data manipulation libraries (pandas, NumPy) and visualization tools (matplotlib, seaborn) empower you to extract insights from data. Master data cleaning, aggregation, and visualization techniques to communicate findings effectively.

3. Machine Learning and AI

Python’s dominance in machine learning and AI is bolstered by libraries such as scikit-learn, TensorFlow, and PyTorch. Develop proficiency in data preprocessing, model training, and deployment to tackle complex predictive analytics and AI-driven solutions.

4. Automation and Scripting

Python’s scripting capabilities facilitate automation tasks, from file management to web scraping and API interactions. Automate repetitive processes to streamline workflows and enhance productivity across various domains.

5. Game Development

Explore Python’s game development frameworks like pygame to create interactive games. From game mechanics and graphics rendering to user interfaces and gameplay logic, Python offers a versatile platform for creating engaging gaming experiences.

6. Desktop Applications

Frameworks like Tkinter and PyQt enable you to develop cross-platform desktop applications with intuitive user interfaces. Master GUI design, event handling, and application integration to build robust desktop solutions tailored to user preferences.

Advancing Your Python Journey

1. Building a Diverse Portfolio

Demonstrate your Python proficiency by undertaking personal projects aligned with your interests and career aspirations. Building a portfolio showcases your capabilities and problem-solving skills, distinguishing you in a competitive job market.

2. Contributing to Open Source

Engage with the open-source community on platforms like GitHub to collaborate on projects, contribute code, and gain practical experience. Open-source contributions expand your skill set, foster collaboration, and enhance your credibility as a developer.

3. Continuous Learning and Growth

Stay updated with Python’s evolving features, industry trends, and emerging technologies through online courses, webinars, and professional networking events. Embrace lifelong learning to remain agile and relevant in the dynamic field of software development.

Conclusion

Mastering Python programming is more than acquiring technical proficiency—it’s about embracing a versatile skill set that empowers you to innovate, solve complex challenges, and drive impactful solutions across industries. By following this comprehensive guide, you can embark on a transformative journey toward Python mastery, equipping yourself with the tools and knowledge to thrive in today’s fast-paced tech landscape.

[awesomehenny888]

5 Sertifikasi SQL Terbaik untuk Meningkatkan Karir Anda di Tahun 2021

Jika Anda ingin bekerja dibidang data seperti data scientist, database administrator dan big data architect structured query language atau SQL adalah salah satu bahasa pemrograman yang wajib Anda kuasai. Akan tetapi, jika Anda ingin cepat direkrut oleh perusahaan besar, serttifikasi SQL wajib dimiliki. Banyak sertifikasi SQL yang bisa Anda dapatkan, apa saja sih? yuk simak ulasannya dibawah ini.

5 Sertifikasi SQL Terbaik

1. Bootcamp MySQL Utama: Udemy

Kursus Udemy ini menyediakan banyak sekali latihan untuk meningkatkan skill Anda, dimulai dengan dasar-dasar MySQL dan berlanjut hingga mengajarkan beberapa konsep lainnya. Kursus ini menyediakan banyak latihan. Terserah Anda untuk mengambil kursus dengan kecepatan yang Anda inginkan.

Kurikulum pelatihan

Ringkasan dan penginstalan SQL: SQL vs. MySQL, penginstalan di Windows dan Mac

Membuat database dan tabel: Pembuatan dan pelepasan tabel, tipe data dasar

Penyisipan data,  NULL, NOT NULL, Primary keys, table constraints

Perintah CRUD: SELECT, UPDATE, DELETE, challenge exercises

Fungsi string: concat, substring, replace, reverse, char length, upper dan lower

Menggunakan karakter pengganti yang berbeda, order by, limit, like, wildcards

Fungsi agregat: count, group by, min, max, sum, avg

Tipe Data secara detail: char, varchar, decimal, float, double, date, time, datetime, now, curdate, curtime, timestamp

Operator logika: not equal, not like, greater than, less than, AND, OR, between, not in, in, case statements

Satu ke banyak: Joins, foreign keys, cross join, inner join, left join, right join, Many to many

Klon data Instagram: nstagram Clone Schema, Users Schema, likes, comments, photos, hashtags, complete schema

Bekerja dengan Big Data : JUMBO dataset, exercises

Memperkenalkan Node: Crash course on Node.js, npm, MySQL, and other languages

Membangun aplikasi web: setting up, connecting Express and MySQL, adding EJS templates, connecting the form

Database triggers: writing triggers, Preventing Instagram Self-Follows With Triggers, creating logger triggers, Managing Triggers, And A Warning

2. Learn SQL Basics for Data Science Specialization

Pelatihan ini bertujuan untuk menerapkan semua konsep SQL yang digunakan untuk ilmu data secara praktis. Kursus pertama dari spesialisasi ini adalah kursus dasar yang akan memungkinkan Anda mempelajari semua pengetahuan SQL yang nantinya akan Anda perlukan untuk kursus lainnya. Dalam pelatihan ini akan ada empat kursus:

SQL untuk Ilmu Data.

Data Wrangling, Analisis, dan Pengujian AB dengan SQL.

Komputasi Terdistribusi dengan Spark SQL.

SQL untuk Proyek Capstone Sains Data.

Kurikulum pelatihan

1. SQL for Data Science (14 hours)

Introduction, selecting, and fetching data using SQL.

Filtering, Sorting, and Calculating Data with SQL.

Subqueries and Joins in SQL.

Modifying and Analyzing Data with SQL.

2. Data Wrangling, Analysis, and AB Testing with SQL

Data of Unknown Quality.

Creating Clean Datasets.

SQL Problem Solving.

Case Study: AB Testing.

3. Distributed Computing with Spark SQL

Introduction to Spark.

Spark Core Concepts.

Engineering Data Pipelines.

Machine Learning Applications of Spark.

4. SQL for Data Science Capstone Project

Project Proposal and Data Selection/Preparation.

Descriptive Stats & Understanding Your Data.

Beyond Descriptive Stats (Dive Deeper/Go Broader).

Presenting Your Findings (Storytelling).

3. Excel to MySQL: Analytic Techniques for Business Specialization

Pelatihan Ini adalah spesialisasi dari Coursera yang bertujuan untuk menyentuh SQL dari sudut pandang bisnis. Jika Anda ingin mendalami ilmu data atau bidang terkait, pelatihan ini sangat bagus. Bersama dengan SQL, Anda juga akan mendapatkan berbagai keterampilan seperti Microsoft Excel, Analisis Bisnis, alat sains data, dan algoritme, serta lebih banyak lagi tentang proses bisnis. Ada lima materi dalam pelatihan ini:

Metrik Bisnis untuk Perusahaan Berdasarkan Data.

Menguasai Analisis Data di Excel.

Visualisasi Data dan Komunikasi dengan Tableau.

Mengelola Big Data dengan MySQL.

Meningkatkan Keuntungan Manajemen Real Estat: Memanfaatkan Analisis Data.

Kurikulum pelatihan

Metrik Bisnis untuk Perusahaan Berdasarkan Data (8 jam): Pengenalan metrik bisnis, pasar analitik bisnis, menerapkan metrik bisnis ke studi kasus bisnis.

Menguasai Analisis Data di Excel (21 jam): Esensi Excel, klasifikasi biner, pengukuran informasi, regresi linier, pembuatan model.

Visualisasi Data dan Komunikasi dengan Tableau (25 jam): Tableau, visualisasi, logika, proyek.

Mengelola Big Data dengan MySQL (41 jam): database relasional, kueri untuk satu tabel, mengelompokkan data, menangani data kompleks melalui kueri.

Meningkatkan Keuntungan Manajemen Real Estat: Memanfaatkan Analisis Data (23 jam): Ekstraksi dan Visualisasi data, pemodelan, arus kas, dan keuntungan, dasbor data.

4. MySQL for Data Analytics and BI

Pelatihan ini mencakup MySQL secara mendalam dan mulai dari dasar-dasar kemudian beralih ke topik SQL lanjutan. Pelatihan ni juga memiliki banyak latihan untuk menyempurnakan pengetahuan Anda.

Kurikulum pelatihan

Introduction to databases, SQL, and MySQL.

SQL theory: SQL as a declarative language, DDL, keywords, DML, DCL, TCL.

Basic terminologies: Relational database, primary key, foreign key, unique key, null values.

Installing MySQL: client-server model, setting up a connection, MySQL interface.

First steps in SQL: SQL files, creating a database, introduction to data types, fixed and floating data types, table creating, using the database, and tables.

MySQL constraints: Primary key constraints, Foreign key constraints, Unique key constraint, NOT NULL

SQL Best practices.

SQL Select, Insert, Update, Delete, Aggregate functions, joins, subqueries, views, Stored routines.

Advanced SQL Topics: Types of MySQL variables, session, and global variables, triggers, user-defined system variables, the CASE statement.

Combining SQL and Tableau.

5, Learning SQL Programming

Pelatihan ini sangat cocok untuk pemula dan mencakup semua aspek penting dari SQL. Pelatihan ini juga mencakup banyak file latihan yang dapat meningkatkan skill Anda.

Kurikulum pelatihan

Memilih data dari database.

Memahami jenis JOIN.

Tipe data, Matematika, dan fungsi yang membantu: Pilih gabungan, ubah data, menggunakan alias untuk mempersingkat nama bidang.

Tambahkan atau ubah data.

Mengatasi kesalahan SQL umum.

Itulah berbagai sertifikasi SQL yang bisa Anda ikuti demi menaikan skill agar cepat diterima oleh perusahaan besar. Tentu saja, pengalaman dan pengetahuan teknis itu penting, tetapi sertifikasi SQL menjadi faktor penentu ketika kandidat dengan profil serupa harus disaring.Baca juga :

3 Manfaat Mengikuti Training SQL Server Jakarta

[rubydailystream]

7 Gems Which Will Make Your Rails Code Look Awesome by @RubyroidLabs

Original article

In Rubyroid Labs we are very passionate about application architecture. Most projects we work here are long-term projects, so if you are not being careful about your application design at some point you will find yourself in a position where in order to add a new feature it's just easier to rebuild the whole project from the scratch. And it's definitely not something you want to face up with.

One of the bad signs that your project is getting sick is that new team members spend a significant amount of time just reading through the source code in order to understand the logic. Today we want share a list of different gems, which in our opinion can help you to organize your code and make your team members smile. 

1. interactor

This library is absolutely fabulous and has always been in our shortlist when talking about writing some complex business logic. What is an interactor? As gem readme says - "an interactor is a simple, single-purpose object, used to encapsulate your application's business logic". You can think of that as a service-objects we all love, but it's a way more than that. Let's take a look on the example:

# app/interactors/create_order.rb class CreateOrder include Interactor def call order = Order.create(order_params) if order.persisted? context.order = order else context.fail! end end def rollback context.order.destroy end end # app/interactors/place_order.rb class PlaceOrder include Interactor::Organizer organize CreateOrder, ChargeCard, SendThankYou end

In this example you probably noticed couple absolutely great features of this gem. The first thing is that you are able to organize your simple interactors to an executable chain, which will be executed in proper order. Special variable context is being used to share states between different interactors. The second thing is that if one of the interactors fails for some reason, all previous will be rolled-back. You could see that rollback on CreateOrder, which will drop an order if ChargeCard or SendThankYou fails. It is so cool, isn't it?

2. draper

If you have ever used custom Rails-helpers, you know how messy they become overtime. And in most cases we use them to display some data in a more fancy way. That's where decorator design pattern can help us. Let's take a look at a draper syntax, which is pretty self-explanatory:

# app/controllers/articles_controller.rb def show @article = Article.find(params[:id]).decorate end # app/decorators/article_decorator.rb class ArticleDecorator < Draper::Decorator delegate_all def publication_status if published? "Published at #{published_at}" else "Unpublished" end end def published_at object.published_at.strftime("%A, %B %e") end end # app/views/articles/show.html.erb <%= @article.publication_status %>

In the code above you can see that our goal was to show published_at attribute in specific format. In classical rails-way we have 2 options of how to do that. First one is just to write a special helper. The problem with this is that all helpers live under the namespace and as project longs you can face up with some weird name collision, which is extremely hard to debug. The second one is to create a new method inside the model and use that method instead. This solution also feels wrong since it breaks model class responsibility. By default models are responsible for interaction with data, other than representation of that data. That's why using draper in this scenario is a more elegant way to achieve our goal.

Be sure that you check another stunning article:

19 Ruby on Rails Gems which Can Amaze

3. virtus

Sometimes using a simple ruby object is not enough for you. Imagine you have some complex form on a page, where different pieces form should be saved as different models in the database. That's where virtus can help you. Let's take a look at the example:

class User include Virtus.model attribute :name, String attribute :age, Integer attribute :birthday, DateTime end user = User.new(:name => 'Piotr', :age => 31) user.attributes # => { :name => "Piotr", :age => 31, :birthday => nil } user.name # => "Piotr" user.age = '31' # => 31 user.age.class # => Fixnum user.birthday = 'November 18th, 1983' # => #<DateTime: 1983-11-18T00:00:00+00:00 (4891313/2,0/1,2299161)> # mass-assignment user.attributes = { :name => 'Jane', :age => 21 } user.name # => "Jane" user.age # => 21

As you can see, virtus looks similar to standard {OpenStruct} class, but gives you many more features. You should definitely play around to explore them all.

This gem could be a good start for you, but if you are looking for more advanced techniques - definitely go and check dry-types, dry-struct and dry-validation.

4. cells

If you are not familiar with Nick Sutterer Ruby on Rails advanced architecture, you should definitely check that out. Not all of us ready to apply this whole concept to their existing applications. But sometimes your views become really complicated, with different conditions applied for different types of user and etc. That's where cells gem could help. It allows moving part of you views to isolated components, which are just regular ruby-classes. Let's take a look at code sample:

# app/cells/comment_cell.rb class CommentCell < Cell::ViewModel property :body property :author def show render end private def author_link link_to "#{author.email}", author end end # app/cells/comment/show.html.erb <h3>New Comment</h3> <%= body %> By <%= author_link %> # app/controllers/dashboard_controller.rb class DashboardController < ApplicationController def index @comments = Comment.recent end end # app/controllers/dashboard/index.html.erb <% @comments.each do |comment| %> <%= cell(:comment, comment) %> <% end %>

In this example we want to display recent comments on our dashboard. Imagine all comments should be displayed identical on our application. Rails will use some shared partial for rendering. But instead of doing that we use CommentCell object. You can think of that object as a combination of draper we talked about before with ability to render views. But of course it has  many more features. Check their README to learn more about all options.

5. retryable

All modern web application do have different types of integrations. Sometimes it's made through solid API calls, sometimes you have to upload a file to FTP or even use some binary protocol. Problem with all integrations is that sometimes their calls just fail. In some cases - it fails without any reasons. And the best thing you can do is just to try again. Thumbs up if you have ever had to do something like this:

begin result = YetAnotherApi::Client.get_info(params) rescue YetAnotherApi::Exception => e retries ||= 0 retries += 1 raise e if retries > 5 retry end

Here is where retryable can help you. Let's take a look at how we can rewrite the example above using that gem:

Retryable.retryable(tries: 5, on: => YetAnotherApi::Exception) do result = YetAnotherApi::Client.get_info(params) end

It looks much nicer, doesn't it? Check that gem out for other scenarios it supports.

6. decent_exposure

If you are not a big fan of using magic - this library is not for you. But in some applications we definitely have a lot of duplications for very simple and standard CRUD actions. That's where decent_exposure could help you. Let's imagine that we are creating new controllers to manage things. That's how scaffold will look for us:

class ThingsController < ApplicationController before_action :set_thing, only: [:show, :edit, :update, :destroy] def index @things = Thing.all end def show end def new @thing = Thing.new end def edit end def create @thing = Thing.new(thing_params) respond_to do |format| if @thing.save format.html { redirect_to @thing, notice: 'Thing was successfully created.' } else format.html { render :new } end end end def update respond_to do |format| if @thing.update(thing_params) format.html { redirect_to @thing, notice: 'Thing was successfully updated.' } else format.html { render :edit } end end end def destroy @thing.destroy respond_to do |format| format.html { redirect_to things_url, notice: 'Thing was successfully destroyed.' } end end private def set_thing @thing = Thing.find(params[:id]) end def thing_params params.require(:thing).permit(:for, :bar) end end

We can't say that 60 lines of code is not too much. But as rubyists we always want it to be as minimalistic as possible. Let's take a look at how it could be transformed using decent_exposure:

class ThingsController < ApplicationController expose :things, ->{ Thing.all } expose :thing def create if thing.save redirect_to thing_path(thing) else render :new end end def update if thing.update(thing_params) redirect_to thing_path(thing) else render :edit end end def destroy thing.destroy redirect_to things_path end private def thing_params params.require(:thing).permit(:foo, :bar) end end

Yakes! Now it's a little more than 30 lines of code without losing any functionality. As you could notice - all magic is brought by expose method. Check this gem documentation for a better understanding of how things work under the hood.

7. groupdate

Every developer knows that dealing with different time zones is a hard thing. Especially when you are trying to write some aggregation on your database. It always gave me hard time when someone asked: "Could I get how many users we are getting every day this month excluding free users" or similar. Now you can stop worrying about such requests and just use gem. Here is an example:

User.paid.group_by_week(:created_at, time_zone: "Pacific Time (US & Canada)").count # { # Sun, 06 Mar 2016 => 70, # Sun, 13 Mar 2016 => 54, # Sun, 20 Mar 2016 => 80 # }

Hope you enjoyed some of these libraries. Let us know if you have any other tools in your mind which can help to write more expressive and awesome code.