Category Archives: How to Fix

C++ string substr()

Common member function

< string>

std::string::substr

string substr (size_t pos = 0, size_t len = npos) const;

Produce substring
Returns a new one
A copy of the String object that is initialized to a substring of the String object.

The
substring is the part of the object that starts at character position pos and spans len characters (or up to the end of the string, whichever comes first).

parameter

pos

The position of the first character is copied as a substring.

this function returns an empty string if this is equal to the length of the string.

if this is greater than the length of the string, it will throw out_of_range.

note: the first character is represented as the value 0 (not 1).

len

The number of characters included in the subscript (if the string is short, as many characters as possible can be used as needed).

string :: non-profit value represents all characters up to the end of the string.

size_t is an unsigned integral type (the same as member type)
string::size_type).

The return value A
String Object with a substring of this object.

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

// string::substr
#include <iostream>
#include <string>

int main ()
{
  std::string str="We think in generalities, but we live in details.";
                                           // (quoting Alfred N. Whitehead)

  std::string str2 = str.substr (3,5);     // "think"

  std::size_t pos = str.find("live");      // position of "live" in str

  std::string str3 = str.substr (pos);     // get from "live" to the end

  std::cout << str2 << ' ' << str3 << '\n';

  return 0;
}
  

Output:

think live in details.

Using Unrar to decompress rar file

The archiving tool on the Mac is unable to unzip the RAR file, so unRAR in the terminal can be used to resolve the problem.
The steps are as follows:
1. Install UNrAR using Homebrew (see Homebrew for installation and use)

$ brew install unrar
==> Downloading http://www.rarlab.com/rar/unrarsrc-5.0.12.tar.gz
######################################################################## 100.0%
==> make

2. CD to the RAR file directory, and then enter the following command in the terminal:

$ unrar x Httpclient_jar.rar

Results:

or

$ unrar e Httpclient_jar.rar

The results are as follows:

Clearly the former is better.

Android studio push project to GitHub

Introduction :Android Studio itself supports Github. So it’s easy to push direct code onto Github.

Prepare _ configuration account
Import the Git
Settings -> Version Control -> Set Git directory

Set up Github
After setting, click Test to Test.

Prepare 2_ create project
Create the As project

Create Github’s repository

Add Git management to the As project


Add the address of the Push
1. Right mouse button in the testProject created = “gitBash

Add Git management to files in your Git directory

Then commit

Then push


OK
If all goes well, you can see that the document was submitted successfully.

SQL-DataCamp-Analyzing Business Data in SQL

SQL-DataCamp-Analyzing Business Data in SQL
1. Revenue, Cost, and Profit
1.1 Revenue (video)
1.2 Revenue per customer
Instruction:
Write the expression for revenue. Keep only the records of user ID 15.

-- Calculate revenue
SELECT SUM(meal_price * order_quantity) AS revenue
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
-- Keep only the records of customer ID 15
WHERE user_id = 15;

1.3 Revenue per week
Instruction:
Write the expression for revenue. Keep only the records of June 2018.

SELECT DATE_TRUNC('week', order_date) :: DATE AS delivr_week,
       -- Calculate revenue
       SUM(meal_price * order_quantity) AS revenue
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
-- Keep only the records in June 2018
WHERE DATE_TRUNC('month', order_date) :: DATE = '2018-06-01'
GROUP BY delivr_week
ORDER BY delivr_week ASC;

1.4 Cost and Common Table Expressions (CTEs) (video)
1.5 Total cost
1.6 Top meals by cost
Instruction:
Calculate cost per meal ID.Set the LIMIT to 5.

SELECT
  -- Calculate cost per meal ID
  meals.meal_id,
  SUM(meal_cost * stocked_quantity) AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY meals.meal_id
ORDER BY cost DESC
-- Only the top 5 meal IDs by purchase cost
LIMIT 5;

1.7 Using CTEs
Instruction 1:
Calculate cost per month.

SELECT
  -- Calculate cost
  DATE_TRUNC('month', stocking_date)::DATE AS delivr_month,
  SUM(meal_cost * stocked_quantity) AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY delivr_month
ORDER BY delivr_month ASC;

Instruction 2:
Wrap the query you just wrote in a CTE named monthly_cost.

-- Declare a CTE named monthly_cost
WITH monthly_cost AS (
  SELECT
    DATE_TRUNC('month', stocking_date)::DATE AS delivr_month,
    SUM(meal_cost * stocked_quantity) AS cost
  FROM meals
  JOIN stock ON meals.meal_id = stock.meal_id
  GROUP BY delivr_month)

SELECT *
FROM monthly_cost;

Instruction 3:
Now that you’ve set up the monthly_cost CTE, find the average cost incurred before September 2018.

-- Declare a CTE named monthly_cost
WITH monthly_cost AS (
  SELECT
    DATE_TRUNC('month', stocking_date)::DATE AS delivr_month,
    SUM(meal_cost * stocked_quantity) AS cost
  FROM meals
  JOIN stock ON meals.meal_id = stock.meal_id
  GROUP BY delivr_month)

SELECT
  -- Calculate the average monthly cost before September
  AVG(cost)
FROM monthly_cost
WHERE delivr_month < '2018-09-01';

1.8 Profit (video)
1.9 Profit per eatery
Instruction:
Calculate revenue per eatery in the revenue CTE. Calculate cost per eatery in the cost CTE. Join the two CTEs and calculate profit per eatery.

WITH revenue AS (
  -- Calculate revenue per eatery
  SELECT eatery,
         SUM(meal_price * order_quantity) AS revenue
    FROM meals
    JOIN orders ON meals.meal_id = orders.meal_id
   GROUP BY eatery),

  cost AS (
  -- Calculate cost per eatery
  SELECT eatery,
         SUM(meal_cost * stocked_quantity) AS cost
    FROM meals
    JOIN stock ON meals.meal_id = stock.meal_id
   GROUP BY eatery)

   -- Calculate profit per eatery
   SELECT revenue.eatery,
          revenue - cost AS profit
     FROM revenue
     JOIN cost ON revenue.eatery = cost.eatery
    ORDER BY profit DESC;

1.10 Profit per month
Instruction:
Calculate revenue per month in the revenue CTE. Calculate cost per month in the cost CTE. Join the two CTEs and calculate profit per month.

-- Set up the revenue CTE
WITH revenue AS ( 
	SELECT
		DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
		SUM(meal_price * order_quantity) AS revenue
	FROM meals
	JOIN orders ON meals.meal_id = orders.meal_id
	GROUP BY delivr_month),
-- Set up the cost CTE
  cost AS (
 	SELECT
		DATE_TRUNC('month', stocking_date) :: DATE AS delivr_month,
		SUM(meal_cost * stocked_quantity) AS cost
	FROM meals
    JOIN stock ON meals.meal_id = stock.meal_id
	GROUP BY delivr_month)
-- Calculate profit by joining the CTEs
SELECT
	revenue.delivr_month,
	revenue - cost AS profit
FROM revenue
JOIN cost ON revenue.delivr_month = cost.delivr_month
ORDER BY revenue.delivr_month ASC;

2. User-centric KPIs
2.1 Registrations and active users (video)
2.2 Registrations by month
Instruction 1:
Return a table of user IDs and their registration dates. Order by user_id in ascending order.

SELECT
  -- Get the earliest (minimum) order date by user ID
  user_id,
  MIN(order_date) AS reg_date
FROM orders
GROUP BY user_id
-- Order by user ID
ORDER BY user_id ASC;

Instruction 2:
Wrap the query you just wrote in a CTE named reg_dates. Using reg_dates, return a table of registrations by month.

-- Wrap the query you wrote in a CTE named reg_dates
WITH reg_dates AS (
  SELECT
    user_id,
    MIN(order_date) AS reg_date
  FROM orders
  GROUP BY user_id)

SELECT
  -- Count the unique user IDs by registration month
  DATE_TRUNC('month', reg_date) :: DATE AS delivr_month,
  COUNT(DISTINCT user_id) AS regs
FROM reg_dates
GROUP BY delivr_month
ORDER BY delivr_month ASC;

2.3 Monthly active users (MAU)
Instruction:
Select the month by truncating the order dates. Calculate MAU by counting the users for every month. Order by month in ascending order.

SELECT
  -- Truncate the order date to the nearest month
  DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
  -- Count the unique user IDs
  COUNT(DISTINCT user_id) AS mau
FROM orders
GROUP BY delivr_month
-- Order by month
ORDER BY delivr_month ASC;

2.4 Window functions (video)
2.5 Registrations running total
Instruction 1:
Select the month and the registrations in each month. Order by month in ascending order.

WITH reg_dates AS (
  SELECT
    user_id,
    MIN(order_date) AS reg_date
  FROM orders
  GROUP BY user_id)

SELECT
  -- Select the month and the registrations
  DATE_TRUNC('month', reg_date)  :: DATE AS delivr_month,
  COUNT(DISTINCT user_id) AS regs
FROM reg_dates
GROUP BY delivr_month
-- Order by month in ascending order
ORDER BY delivr_month;

Instruction 2:
Return a table of the registrations running total by month. Order by month in ascending order.

WITH reg_dates AS (
  SELECT
    user_id,
    MIN(order_date) AS reg_date
  FROM orders
  GROUP BY user_id),

  regs AS (
  SELECT
    DATE_TRUNC('month', reg_date) :: DATE AS delivr_month,
    COUNT(DISTINCT user_id) AS regs
  FROM reg_dates
  GROUP BY delivr_month)

SELECT
  -- Calculate the registrations running total by month
  delivr_month,
  SUM(regs) OVER (ORDER BY delivr_month ASC) AS regs_rt
FROM regs
-- Order by month in ascending order
ORDER BY delivr_month ASC;

2.6 MAU monitor (1)
Instruction:
Select the month and the MAU. Fetch the previous month’s MAU. Order by month in ascending order.

WITH mau AS (
  SELECT
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    COUNT(DISTINCT user_id) AS mau
  FROM orders
  GROUP BY delivr_month)

SELECT
  -- Select the month and the MAU
  delivr_month,
  mau,
  COALESCE(
    LAG(mau) OVER (ORDER BY delivr_month ASC),
0)  AS last_mau
FROM mau
-- Order by month in ascending order
ORDER BY delivr_month ASC;

2.7 Growth (video)
2.8 MAU monitor (2)
Instruction:
Fetch the previous month’s MAU in the mau_with_lag CTE…Select the month and the delta between its MAU and the previous month’s MAU. Order by month in ascending order.

WITH mau AS (
  SELECT
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    COUNT(DISTINCT user_id) AS mau
  FROM orders
  GROUP BY delivr_month),

  mau_with_lag AS (
  SELECT
    delivr_month,
    mau,
    -- Fetch the previous month's MAU
    COALESCE(
    LAG(mau) OVER (ORDER BY delivr_month ASC),
    0) AS last_mau
  FROM mau)

SELECT
  -- Calculate each month's delta of MAUs
  delivr_month,
  mau - last_mau AS mau_delta
FROM mau_with_lag
-- Order by month in ascending order
ORDER BY delivr_month;

2.9 MAU monitor (3)
Instruction:
Select the month and its MoM MAU growth rate. Order by month in ascending order.

WITH mau AS (
  SELECT
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    COUNT(DISTINCT user_id) AS mau
  FROM orders
  GROUP BY delivr_month),

  mau_with_lag AS (
  SELECT
    delivr_month,
    mau,
    GREATEST(
      LAG(mau) OVER (ORDER BY delivr_month ASC),
    1) AS last_mau
  FROM mau)

SELECT
  -- Calculate the MoM MAU growth rates
  delivr_month,
  ROUND(
  (mau - last_mau) :: NUMERIC/last_mau,
  2) AS growth
FROM mau_with_lag
-- Order by month in ascending order
ORDER BY delivr_month;

2.10 Order growth rate
Instruction:
Count the unique orders per month. Fetch each month’s previous and current orders. Return a table of MoM order growth rates.

WITH orders AS (
  SELECT
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    --  Count the unique order IDs
    COUNT(DISTINCT order_id) AS orders
  FROM orders
  GROUP BY delivr_month),

  orders_with_lag AS (
  SELECT
    delivr_month,
    -- Fetch each month's current and previous orders
    orders,
    COALESCE(
      LAG(orders) OVER (ORDER BY delivr_month ASC),
    1) AS last_orders
  FROM orders)

SELECT
  delivr_month,
  -- Calculate the MoM order growth rate
  ROUND(
    (orders - last_orders) :: NUMERIC/last_orders,
  2) AS growth
FROM orders_with_lag
ORDER BY delivr_month ASC;

2.11 Retention (video)
2.12 New, retained, and resurrected users
2.13 Retention rate
Instruction:
Select the month column from user_monthly_activity, and calculate the MoM user retention rates. Join user_monthly_activity to itself on the user ID and the month, pushed forward one month.

WITH user_monthly_activity AS (
  SELECT DISTINCT
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    user_id
  FROM orders)

SELECT
  -- Calculate the MoM retention rates
  previous.delivr_month,
  ROUND(
  COUNT(DISTINCT current.user_id) :: NUMERIC /
  GREATEST(COUNT(DISTINCT previous.user_id), 1),
  2) AS retention_rate
FROM user_monthly_activity AS previous
LEFT JOIN user_monthly_activity AS current
-- Fill in the user and month join conditions
ON previous.user_id = current.user_id
AND previous.delivr_month = (current.delivr_month - INTERVAL '1 month')
GROUP BY previous.delivr_month
ORDER BY previous.delivr_month ASC;

3. ARPU, Histograms, and Percentiles
3.1 Unit economics (video)
3.2 Average revenue per user
Instruction 1:
Return a table of user IDs and the revenue each user generated.

SELECT
  -- Select the user ID and calculate revenue
  user_id,
  SUM(meal_price * order_quantity) AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id;

Instruction 2:
Wrap the previous query in a CTE named kpi. Return the average revenue per user (ARPU).

-- Create a CTE named kpi
WITH kpi AS (
  SELECT
    -- Select the user ID and calculate revenue
    user_id,
    SUM(m.meal_price * o.order_quantity) AS revenue
  FROM meals AS m
  JOIN orders AS o ON m.meal_id = o.meal_id
  GROUP BY user_id)
-- Calculate ARPU
SELECT ROUND(AVG(revenue) :: NUMERIC,2) AS arpu
FROM kpi;

3.3 ARPU per week
Instruction:
Store revenue and the number of unique active users by week in the kpi CTE. Calculate ARPU by dividing the revenue by the number of users. Order the results by week in ascending order.

WITH kpi AS (
  SELECT
    -- Select the week, revenue, and count of users
    DATE_TRUNC('week', order_date) :: DATE AS delivr_week,
    SUM(meal_price * order_quantity) AS revenue,
    COUNT(DISTINCT user_id) AS users
  FROM meals AS m
  JOIN orders AS o ON m.meal_id = o.meal_id
  GROUP BY delivr_week)

SELECT
  delivr_week,
  -- Calculate ARPU
   ROUND(revenue :: NUMERIC/GREATEST(users, 1),2) AS arpu
FROM kpi
-- Order by week in ascending order
ORDER BY delivr_week ASC;

3.4 Average orders per user
Instruction :
Store the count of distinct orders and distinct users in the kpi CTE. Calculate the average orders per user.

WITH kpi AS (
  SELECT
    -- Select the count of orders and users
    COUNT(DISTINCT order_id) AS orders,
    COUNT(DISTINCT user_id) AS users
  FROM orders)

SELECT
  -- Calculate the average orders per user
  ROUND(
    orders :: NUMERIC/GREATEST(users, 1),
  2) AS arpu
FROM kpi;

3.5 Histograms (video)
3.6 Histograms of revenue
Instruction:
Store each user ID and the revenue Delivr generates from it in the user_revenues CTE. Return a frequency table of revenues rounded to the nearest hundred and the users generating those revenues.

WITH user_revenues AS (
  SELECT
    -- Select the user ID and revenue
    user_id,
    SUM(meal_price * order_quantity) AS revenue
  FROM meals AS m
  JOIN orders AS o ON m.meal_id = o.meal_id
  GROUP BY user_id)

SELECT
  -- Return the frequency table of revenues by user
  ROUND(revenue :: NUMERIC, -2) AS revenue_100,
  COUNT(DISTINCT user_id) AS users
FROM user_revenues
GROUP BY revenue_100
ORDER BY revenue_100 ASC;

3.7 Histograms of orders
Instruction 1:
Set up the frequency tables query by getting each user’s count of orders.

SELECT
  -- Select the user ID and the count of orders
  user_id,
  COUNT(DISTINCT order_id) AS orders
FROM orders
GROUP BY user_id
ORDER BY user_id ASC
LIMIT 5;

Instruction 2:
Return a frequency table of orders and the count of users with those orders.

WITH user_orders AS (
  SELECT
    user_id,
    COUNT(DISTINCT order_id) AS orders
  FROM orders
  GROUP BY user_id)

SELECT
  -- Return the frequency table of orders by user
  orders,
  COUNT(DISTINCT user_id) AS users
FROM user_orders
GROUP BY orders
ORDER BY orders ASC;

3.8 Bucketing (video)
3.9 Bucketing users by revenue
Instruction:
Store each user ID and the revenue it generates in the user_revenues CTE. Return a table of the revenue groups and the count of users in each group.

WITH user_revenues AS (
  SELECT
    -- Select the user IDs and the revenues they generate
    user_id,
    SUM(meal_price * order_quantity) AS revenue
  FROM meals AS m
  JOIN orders AS o ON m.meal_id = o.meal_id
  GROUP BY user_id)

SELECT
  -- Fill in the bucketing conditions
  CASE
    WHEN revenue < 150  THEN 'Low-revenue users'
    WHEN revenue < 300 THEN 'Mid-revenue users'
    ELSE 'High-revenue users'
  END AS revenue_group,
  COUNT(DISTINCT user_id) AS users
FROM user_revenues
GROUP BY revenue_group;

3.10 Bucketing users by orders
Instruction:
Store each user ID and its count of orders in a CTE named user_orders. Set the cut-off point for the low-orders bucket to 8 orders, and set the cut-off point for the mid-orders bucket to 15 orders. Count the distinct users in each bucket.

-- Store each user's count of orders in a CTE named user_orders
WITH user_orders AS (
  SELECT
    user_id,
    COUNT(DISTINCT order_id) AS orders
  FROM orders
  GROUP BY user_id)

SELECT
  -- Write the conditions for the three buckets
  CASE
    WHEN orders < 8 THEN 'Low-orders users'
    WHEN orders < 15 THEN 'Mid-orders users'
    ELSE 'High-orders users'
  END AS order_group,
  -- Count the distinct users in each bucket
  COUNT(DISTINCT user_id) AS users
FROM user_orders
GROUP BY order_group;

3.11 Percentiles (video)
3.12 Revenue quartiles
Instruction:
Store each user ID and the revenue Delivr generates from it in the user_revenues CTE. Calculate the first, second, and third revenue quartile. Calculate the average revenue.

WITH user_revenues AS (
  -- Select the user IDs and their revenues
  SELECT
    user_id,
    SUM(meal_price * order_quantity) AS revenue
  FROM meals AS m
  JOIN orders AS o ON m.meal_id = o.meal_id
  GROUP BY user_id)

SELECT
  -- Calculate the first, second, and third quartile
  ROUND(
    PERCENTILE_CONT(0.25) WITHIN GROUP
    (ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p25,
  ROUND(
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p50,
  ROUND(
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p75,
  -- Calculate the average
  ROUND(AVG(revenue) :: NUMERIC, 2) AS avg_revenue
FROM user_revenues;

3.13 Interquartile range
Instruction 1:
Return a table of user IDs and generated revenues for each user.

SELECT
  -- Select user_id and calculate revenue by user
  user_id,
  SUM(meal_price * order_quantity) AS revenue
FROM meals AS m
JOIN orders AS o ON m.meal_id = o.meal_id
GROUP BY user_id;

Instruction 2:
Wrap the previous query in a CTE named user_revenues. Calculate the first and third revenue quartiles.

-- Create a CTE named user_revenues
WITH user_revenues AS (
  SELECT
    -- Select user_id and calculate revenue by user 
    user_id,
    SUM(m.meal_price * o.order_quantity) AS revenue
  FROM meals AS m
  JOIN orders AS o ON m.meal_id = o.meal_id
  GROUP BY user_id)

SELECT
  -- Calculate the first and third revenue quartiles
   ROUND(
    PERCENTILE_CONT(0.25) WITHIN GROUP
    (ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p25,
   ROUND(
    PERCENTILE_CONT(0.75) WITHIN GROUP
    (ORDER BY revenue ASC) :: NUMERIC,
2) AS revenue_p75
FROM user_revenues;

Instruction 3:
Count the number of distinct users. Filter out all users outside the IQR.

WITH user_revenues AS (
  SELECT
    -- Select user_id and calculate revenue by user 
    user_id,
    SUM(m.meal_price * o.order_quantity) AS revenue
  FROM meals AS m
  JOIN orders AS o ON m.meal_id = o.meal_id
  GROUP BY user_id),

  quartiles AS (
  SELECT
    -- Calculate the first and third revenue quartiles
    ROUND(
      PERCENTILE_CONT(0.25) WITHIN GROUP
      (ORDER BY revenue ASC) :: NUMERIC,
    2) AS revenue_p25,
    ROUND(
      PERCENTILE_CONT(0.75) WITHIN GROUP
      (ORDER BY revenue ASC) :: NUMERIC,
    2) AS revenue_p75
  FROM user_revenues)

SELECT
  -- Count the number of users in the IQR
  COUNT(DISTINCT user_id) AS users
FROM user_revenues
CROSS JOIN quartiles
-- Only keep users with revenues in the IQR range
WHERE revenue :: NUMERIC >= revenue_p25
  AND revenue :: NUMERIC <= revenue_p75;

4. Generating an Executive Report
4.1 Survey of useful functions (video)
4.2 Formatting dates
Instruction:
Select the order date. Format the order date so that 2018-06-01 is formatted as Friday 01, June 2018.

SELECT DISTINCT
  -- Select the order date
  order_date,
  -- Format the order date
  TO_CHAR(order_date, 'FMDay DD, FMMonth YYYY') AS format_order_date
FROM orders
ORDER BY order_date ASC
LIMIT 3;

4.3 Rank users by their count of orders
Instruction 1:
Keep only the orders in August 2018.

SELECT
  user_id,
  COUNT(DISTINCT order_id) AS count_orders
FROM orders
-- Only keep orders in August 2018
WHERE DATE_TRUNC('month', order_date) = '2018-08-01'
GROUP BY user_id;

Instruction 2:
Wrap the previous query in a CTE named user_count_orders. Select the user ID and rank all user IDs by the count of orders in descending order. Only keep the top 3 users by their count of orders.

-- Set up the user_count_orders CTE
WITH user_count_orders AS (
  SELECT
    user_id,
    COUNT(DISTINCT order_id) AS count_orders
  FROM orders
  -- Only keep orders in August 2018
  WHERE DATE_TRUNC('month', order_date) = '2018-08-01'
  GROUP BY user_id)

SELECT
  -- Select user ID, and rank user ID by count_orders
  user_id,
  RANK() OVER (ORDER BY count_orders DESC) AS count_orders_rank
FROM user_count_orders
ORDER BY count_orders_rank ASC
-- Limit the user IDs selected to 3
LIMIT 3;

4.4 Pivoting (video)
4.5 Pivoting user revenues by month
Instruction 1:
Enable CROSSTAB() from tablefunc.
Declare the new pivot table’s columns, user ID and the first three months of operation.

-- Import tablefunc
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB($$
  SELECT
    user_id,
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    SUM(meal_price * order_quantity) :: FLOAT AS revenue
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
 WHERE user_id IN (0, 1, 2, 3, 4)
   AND order_date < '2018-09-01'
 GROUP BY user_id, delivr_month
 ORDER BY user_id, delivr_month;
$$)
-- Select user ID and the months from June to August 2018
AS ct (user_id INT,
       "2018-06-01" FLOAT,
       "2018-07-01" FLOAT,
       "2018-08-01" FLOAT)
ORDER BY user_id ASC;

4.6 Costs
Instruction 1:
Select the eatery and calculate total cost per eatery. Keep only the records after October 2018.

SELECT
  -- Select eatery and calculate total cost
  eatery,
  DATE_TRUNC('month', stocking_date) :: DATE AS delivr_month,
  SUM(meal_cost * stocked_quantity):: FLOAT AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
-- Keep only the records after October 2018
WHERE stocking_date	 > '2018-10-01'
GROUP BY eatery, delivr_month
ORDER BY eatery, delivr_month;

Instruction 2:
Enable CROSSTAB from tablefunc. Declare the new pivot table’s columns, the eatery and the last two months of operation.

-- Import tablefunc
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB($$
  SELECT
    -- Select eatery and calculate total cost
    eatery,
    DATE_TRUNC('month', stocking_date) :: DATE AS delivr_month,
    SUM(meal_cost * stocked_quantity) :: FLOAT AS cost
  FROM meals
  JOIN stock ON meals.meal_id = stock.meal_id
  -- Keep only the records after October 2018
  WHERE DATE_TRUNC('month', stocking_date) > '2018-10-01'
  GROUP BY eatery, delivr_month
  ORDER BY eatery, delivr_month;
$$)

-- Select the eatery and November and December 2018 as columns
AS ct (eatery TEXT,
       "2018-11-01" FLOAT,
       "2018-12-01" FLOAT)
ORDER BY eatery ASC;

4.6 Producing executive reports (video)
4.7 Report readability
4.8 Executive report
Instruction 1:
Fill in the format string that formats 2018-06-01 as Q2 2018. Count the ordering users by eatery and by quarter.

SELECT
  eatery,
  -- Format the order date so "2018-06-01" becomes "Q2 2018"
  TO_CHAR(order_date, '"Q"Q YYYY') AS delivr_quarter,
  -- Count unique users
  COUNT(DISTINCT user_id) AS users
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY eatery, delivr_quarter
ORDER BY delivr_quarter, users;

Instruction 2:
Select the eatery and the quarter from the CTE. Assign a rank to each row, with the top-most rank going to the row with the highest orders.

WITH eatery_users AS  (
  SELECT
    eatery,
    -- Format the order date so "2018-06-01" becomes "Q2 2018"
    TO_CHAR(order_date, '"Q"Q YYYY') AS delivr_quarter,
    -- Count unique users
    COUNT(DISTINCT user_id) AS users
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
  GROUP BY eatery, delivr_quarter
  ORDER BY delivr_quarter, users)

SELECT
  -- Select eatery and quarter
  eatery,
  delivr_quarter,
  -- Rank rows, partition by quarter and order by users
  RANK() OVER
    (PARTITION BY delivr_quarter
     ORDER BY users DESC) :: INT AS users_rank
FROM eatery_users
ORDER BY delivr_quarter, users_rank;

Instruction 3:
Import the tablefunc extension. Pivot the table by quarter. Select the new columns from the pivoted table.

-- Import tablefunc
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Pivot the previous query by quarter
SELECT * FROM CROSSTAB ($$
  WITH eatery_users AS  (
    SELECT
      eatery,
      -- Format the order date so "2018-06-01" becomes "Q2 2018"
      TO_CHAR(order_date, '"Q"Q YYYY') AS delivr_quarter,
      -- Count unique users
      COUNT(DISTINCT user_id) AS users
    FROM meals
    JOIN orders ON meals.meal_id = orders.meal_id
    GROUP BY eatery, delivr_quarter
    ORDER BY delivr_quarter, users)

  SELECT
    -- Select eatery and quarter
    eatery,
    delivr_quarter,
    -- Rank rows, partition by quarter and order by users
    RANK() OVER
      (PARTITION BY delivr_quarter
       ORDER BY users DESC) :: INT AS users_rank
  FROM eatery_users
  ORDER BY eatery, delivr_quarter;
$$)
-- Select the columns of the pivoted table
AS  ct (eatery TEXT,
        "Q2 2018" INT,
        "Q3 2018" INT,
        "Q4 2018" INT)
ORDER BY "Q4 2018";

4.9 Course recap

Data analysis to obtain Yahoo stock data: some problems are encountered when using panda datareader (cannot import name ‘is_ list_ Like ‘problem)

1. Install the Quilt-Datareader dependency package:
Because Python 3.6 was already installed on my computer and I had learned about crawlers first, I already had a Python environment. Now I learn data scientific data analysis and install Anaconda again. In order not to cause chaos in my computer environment, after installing Anaconda, the environment variable of Anaconda is not written into the system environment variable. Then, to install the third party and use imperative operation, I need to open CMD in the path of the installation directory:
1.1 Use anaconda’s Python environment
In the root directory of the Anaconda installation, open CMD, enter Python, and this is the Python environment where the Anaconda is executed.
1.2 Use the conda command to install dependency packages or to view dependencies and other commands to be executed by Conda
Use the conda command: go to Anaconda3- > Library -> Execute under bin path (open CMD)
Open CMD in the directory above and execute conda commands, such as conda Install Augmentation-Datareader, etc
Check the anaconda in the environment depend on the package: conda list;
2 use the pandas – the datareader
Error importing pandas_datareader: cannot import name ‘is_list_like’
With this in mind, we can look at the reasons for the import error:

It says a file called Fred.py. We still don’t know what causes it. Look for the solution on the Internet and find an effective way to modify a statement of the file:
The first line of the original Fred.py file into the opening import is this from pandas.core.common import is_list_like
We modify this sentence to: from Safari. API. Types import is_list_like
When you go back to import, you find that the import was successful.
2.2 Use pandas_datareader to get Yahoo stock data and find an error.
pandas_datareader.get_data_yahoo(‘BABA’)

The reason has been stated in the content. This API interface has been abandoned. After checking the data, I found that it may be the reason why Yahoo was acquired by Verizon in 2017.
But there’s a new solution online:
We need to install an additional dependency package: fix_yahoo_finance (conda install again)
Import FIX_yahoo_Finance as fy after successful installation
From pandas_datareader import data as PDR

import fix_yahoo_finance as yf
yf.pdr_override() # needs to call this function

# to get data
data = PDR get_data_yahoo(“SPY”, start=”2017-01-01″, end=”2017-04-30″)
data = pdr.get_data_yahoo([“SPY”, “IWM”], start=”2017-01-01″, end=”2017-04-30″)
Tips: On Conda Install fix_yahoo_Finance, you may run into PackageNotFoundError: “Package missing in current channels”.
Solution 1:
Find the dependencies you want to install
Anaconda Search-t Conda you want to install packages such as Yahoo
(If there is no Python variable with anaconda added in the environment variable, it will be executed under the scripts installed)

As shown in the figure, you can see the installation package found (the result of the fuzzy search), but of course this package may not be searched, such as the one at the beginning (the unboxed part at the top).
If you do, you’ll see anaconda show < USER/PACKAGE> This is used to further view the installation information you want to install.
For example, I select one to view
anaconda show postelrich/yahoo-finance

The package will be given some information in the front, the last will be given how to install the directory, copy it, in conda is ok:
Conda install yahoo – channel https://conda.anaconda.org/postelrich – finance
Solution 2: Install locally
What do you do if you can’t find the dependencies you want to install?Download the file and install it locally.
For example, I used to use AlphaGam-DatareadErr to obtain stock data from Yahoo’s interface when I acquired the stock, but unfortunately I cannot do so now. The interface has been removed.
A search on the Internet found a FIX_yahoo_finance package can achieve the previous operation, so immediately go to conda Fix_yahoo_finance, found that the installation cannot, anaconde Search can not find this package, so I went to fix_yahoo_finance API website to find the source file download address, download to the local.
Documents: https://pypi.org/project/fix-yahoo-finance/
Download Address:
https://files.pythonhosted.org/packages/0a/96/d44330e427f5368cb8abd25997b72956a31b52073d285c4d5cd56e5fdc17/fix-yahoo-finance-0.0.22.tar.gz
Depending on the package, download and unzip the file setup.py

We can install this package locally. How to install it
CD into the fix_yahoo_finance directory, install it with Python setup.py install, and it will automatically install the dependency into the environment.
Don’t be fooled by my Pythonana, this is because I have two Pythons installed on my computer at the same time, as Mentioned earlier, so I’ve changed one python command to Python so that my Python doesn’t get in a fight. If you haven’t changed it, you can ignore it, and just go ahead and install it in Python.

At this point, we use:
From pandas_datareader import data as PDR

import fix_yahoo_finance as yf
yf.pdr_override() # needs to call this function

# to get data
data = PDR get_data_yahoo(“SPY”, start=”2017-01-01″, end=”2017-04-30″)
data = pdr.get_data_yahoo([“SPY”, “IWM”], start=”2017-01-01″, end=”2017-04-30″)
The stock data in Yahoo can be obtained normally, and the request network to obtain data is also relatively stable.

Summary of IOS storyboard unwind segues

When using storyboard development, we often add a button on a scene, drag the button to the page we want to associate with, and finally choose push to jump. So scene_A and scene_B have a “sequence” jump. But sometimes, you want to trigger an action from scene_B to jump back to scene_A. If you do it the same way you did it, you’re going to have a problem. Because the scene_a that jumps back is not the scene_a that it was. So we’re going to have to use the unwind segue to our storyboard. Write a little summary of the demo:
Create the storyboard shown in the figure below. RedViewController pushes to YellowViewController, YellowViewController pushes to BlueViewController, and BlueViewController modal to GreenViewController. You can return RedViewController from YellowViewController and YellowViewController and RedViewController from BlueViewController. Of course, you can also go back to the BlueViewController from GreenViewController.

Back to the code. Since storyboard is used, the jump of push and model saves writing code, similarly, the jump of “return” also saves code.
RedViewController. M

– (IBAction)unwindSegueToRedViewController:(UIStoryboardSegue *)segue {
    
}
It’s important to note that the return value of this method must be an ibaction, and the argument must be a uistoryboardsegue. As for why, the following code will show.
Go back to your storyboard file and observe that the RedViewController scene has a green button below it (not because you wrote the code above, but because it’s always there).

Next, right click the exit button (green button), will appear just write method unwindseguetoredviewcontroller:

Select this method and drag and drop to the Back RedVC button on the YellowViewController.

After letting go, an Action prompt appears near Back RedVC and is selected. In this way, when you click the Back Red VC button on the YellowViewController, it will jump back to the Red View Controller. Let me compile it. There’s no problem with insurance. Run the program.
Instructions required:
1. If you like from yellowviewcontroller returns to redviewcontroller, then unwind segue associated methods must be stated in redviewcontroller again, that is the example of – (inaction) unwindtoredviewcontroller (segue uistoryboardsegue *); Note the parameters and return points, and the method name is arbitrary.
2. Right click on the exit button of redviewcontroller (green button) to show the method that unwind segue can associate with, and then go to the button of yellowviewcontroller.

Similarly, the BlueViewController can be returned to the Implementation of The YellowViewController.
In YellowViewoController. M

– (IBAction)unwindToYellowViewController:(UIStoryboardSegue *)segue {
    
}
Right-click the Exit button of The YellowViewController to associate BlueviewControler’s Back YellowVC button.
Also need to realize from the immediate return to redviewcontroller blueviewcontroller, selected redviewcontroller exit button, select the button on the blueviewcontroller unwindtoredviewcontroller associated.
Compile it, no problem, run it, get a feel for it.
Either the YellowViewController or the BlueViewController can be returned to the RedViewController, at which point you need to make a judgment to see where the return is coming from.

- (IBAction)unwindSegueToRedViewController:(UIStoryboardSegue *)segue {
    
    UIViewController *sourceViewController = segue.sourceViewController;
    
    if ([sourceViewController isKindOfClass:[YellowViewController class]]) {
        NSLog(@"from yellow vc");
    }
    else if ([sourceViewController isKindOfClass:[BlueViewController class]]) {
        NSLog(@"from blue vc");
    }
}

And that’s why the argument to a method is a segue, where you get the source View Controller.

Now when you try push, the same thing with modal, you present the GreenViewController from the BlueviewController in modal mode.
To return, you need to write the method associated with the unwind segue in the BlueviewController:

– (IBAction)unwindToBlueViewController:(UIStoryboardSegue *)segue {
    
}
Associated unwindtoblueviewcontroller methods on the corresponding button.
At this point, I’m done with “back” in my storyboard, and I spend most of my time dragging and dropping controls without writing the relevant pop dismiss method at all. That’s one of the strengths of storyboard.
Is it possible to implement it in code?You can.
Select GreenViewController Scene in your storyboard, and on the left you can see the representation of the unwind segue, kind of like the DNA sequence yes, once you select it, the only representation that defines it is the greenUnwind


In greenviewcontroller. M
Method associated with back Code button:

- (IBAction)backCodeBtnTapped:(id)sender {

    [self performSegueWithIdentifier:@"greenUnwind" sender:self];
}

Compile, there are no errors, the program.

Program all code.

Hadoop download and install cloudera virtual machine (VM)

1.  Install VirtualBox.  Go to https://www.virtualbox.org/wiki/Downloads to download and install VirtualBox for your computer. For Windows, select the link “VirtualBox 5.1.4 for Windows hosts x86/amd64”.
2.  Download the Cloudera VM.  Download the Cloudera VM fromhttps://downloads.cloudera.com/demo_vm/virtualbox/cloudera-quickstart-vm-5.4.2-0-virtualbox.zip. The VM is over 4GB, so will take some time to download.
3.  Unzip the Cloudera VM:
Right-click cloudera-quickstart-vm-5.4.2-0-virtualbox.zip and select “Extract All…”
4.  Start VirtualBox.
5.  Begin importing. Import the VM by going to File -> Import Appliance

6.  Click the Folder icon.


7.  Select the cloudera-quickstart-vm-5.4.2-0-virtualbox. ovf from the Folder where you unzipped the VirtualBox VM and click Open.

8.  Click Next to proceed.

9.  Click Import.

10.  The virtual machine image will be imported.  This can take several minutes.

11.  Launch Cloudera VM.  When the importing is finished, the quickstart-vm-5.4.2-0 VM will appear on the left in the VirtualBox window. Select it and click the Start button to launch the VM.

12.  Cloudera VM booting.  It will take several minutes for the Virtual Machine to start. The booting process takes a long time since many Hadoop tools are started.

13.  The Cloudera VM desktop.  Once the booting process is complete, the desktop will appear with a browser.

The simplest way to completely unload cygwin under Windows

Cygwin

[the statement] welcome to reprint, but please keep original article source: http://blog.csdn.net/yelangjueqi/article/details/45199209

This thing is difficult to install, uninstall the same trouble, all kinds of ways tried, but can not remove. After a lot of trial and error, I finally found the easiest way. Remember the setup.exe when you installed Cygwin?You can completely remove Cygwin with this thing! If you forget the setup. Exe this place where, can go to the website (https://cygwin.com/install.html) to download a setup. Exe.

Follow these steps to uninstall Cygwin completely.

Double-click to start setup.exe and follow these steps:

The next step

Next, browse to the local cygwin installation directory: D:\cygwin_again

The next step

Next, and this is an important step, click the cursor position to go to: Uninstall mode

Why can’t Scala shell enter Q to exit

In general, most shell command Windows, such as Python, Scala, some non-relational databases, etc., can be logged out by CTRL + D or CTRL + Z, CTRL + C, which is the most effective method.
If you don’t use the above mandatory exit command, you can also use Quit, exit, etc. However, when I use scala2.12 version, I find that Quit, exit, and Q are all difficult to make. After looking up the data, it can be seen that it is new
Version Scala’s exit command needs to be preceded by a ‘:’, i.e. : Q, : Quit, or sys.exit.
 
Original: http://www.cnblogs.com/mszw/p/6931696.html

Usage of Python dropout

link: https://www.zhihu.com/question/67209417/answer/302434279
Just stepped on the pit, almost cried out TT. — I clearly added a hundred dropout, why the results have not changed
When using F.dropout (nn. Functional. Dropout), it is necessary to set the state parameter of training consistent with the model as a whole.
Such as:

 
    Class DropoutFC(nn.Module): def: (self): super(DropoutFC, self). input): out = self.fc(input) out = F.dropout(out, P =0.5) return out Net = DropoutFC() Net. Train () # train the Net

The f.d.ropout in this code is actually useless because its training state is always the default False. Since F.ropout is only equivalent to an external function referenced, changes in the training status of the whole model will not cause changes in the training status of the function f.ropout. So, here out = F.d ropout (out) is out = out. Ref: https://github.com/pytorch/pytorch/blob/master/torch/nn/functional.py#L535
 
The correct way to use it is to pass the training status parameters of the model into the Dropout function

 
    Class DropoutFC(nn.Module): def: (self): super(DropoutFC, self). Input): out = self.fc(input) out = f.darpout (out, p=0.5, Training =self. Training) return out Net = DropoutFC() Net. Train () # train the Net

 
Or directly using nn. Dropout () (nn) Dropout () is actually the F.d ropout a packing, will also self. Training incoming) Ref: https://github.com/pytorch/pytorch/blob/master/torch/nn/modules/dropout.py#L46

 
    Class DropoutFC(nn. Module): def __init__: super(DropoutFC, self).__init__() self.fc = nn. Linear(100.20) self.dropout = nn. Dropout(p=0.5) def forward(self, input): out = self.fc(input) out = self.dropout(out) return out Net = DropoutFC() Net.train()

Installing PyQt4 in Windows + Python 3.6

As the title suggests, this article only applies to Python3.6 in Windows, and note that PyQt4, not PyQt5, will be installed. This is important because if you’ve ever used Python’s third party drawing libraries, Matplotlib and Seaborn, you’ve noticed that they both rely on PyQt4 (whatever PyQt5 does, it’s PyQt4, not PyQt5. If you have PyQt5 installed but don’t have PyQt4, you still can’t use them). In general, if you are using Anaconda3 as the Python interpreter, the Python version that contains PyQt4 can be viewed using conda list (the Python3.6 version comes with me). If, by some accident, PyQt4 is accidentally uninstalled, you use one of the two libraries mentioned above and it is “No module named PyQt4”, or “Pyqt4.gui, Pyqt4.core”, etc. There are several common ways to solve such problems online:
(1) Download The Windows Installers version of PyQt4 directly from the official website, namely the EXE file, and install it directly. Unfortunately, it looks like the official site has been revamped and I can’t find a ready version of Windows x 32 or x 64 for Python3.6 anyway.
(2) now the official website published the version of window after downloading is an uncompressed version, but not directly can be used, and need to re-make installation, more troublesome.
(3) a commonly used PIP or conda automatic installation does not reveal the existence of readily available resources for the Windows platform.
A very simple and efficient way to do this is to download it directly. WHL, which is available here (http://www.lfd.uci.edu/~gohlke/pythonlibs/#pyqt4), is a convenient way to choose between different Python versions, Windows 64bit or 32bit. After downloading, you can put the file into the Python installation directory, and then enter the CD into the directory under CMD or anaconda prompt. Enter the command, such as: PIP install pyqt4-4.11.4-cp36-cp36m-win_amd64. WHL , and wait for the completion of the installation. At this point, import seaborn as SNS or import matplotlib.pyplot as PLT can be executed normally.