table-race-times-summary

INSERT INTO cycling.race_times_summary (
  race_time, race_date
) VALUES (
  '10:01:18', '2017-04-14'
);

INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('10:15:20', '2017-04-14');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('11:15:38', '2017-04-14');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('12:15:40', '2017-04-14');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('10:01:18', '2018-07-26');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('10:15:20', '2018-07-26');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('11:15:38', '2018-07-26');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('12:15:40', '2018-07-26');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('10:01:18', '2019-03-21');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('10:15:20', '2019-03-21');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('11:15:38', '2019-03-21');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('12:15:40', '2019-03-21');

SELECT * FROM cycling.race_times_summary;

SELECT race_date, FLOOR(race_time, 1h), COUNT(*) FROM cycling.race_times_summary
  GROUP BY race_date, FLOOR(race_time, 1h);

SELECT race_date, race_time FROM cycling.race_times_summary
  GROUP BY race_date;

DESCRIBE TABLE cycling.race_times_summary;

DROP TABLE IF EXISTS cycling.race_times_summary;

CREATE TABLE IF NOT EXISTS cycling.race_times_summary (
  race_date date,
  race_time time,
  PRIMARY KEY (race_date, race_time)
);
// end::table-create[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Create table race_times_summary"

tag="table-describe-base[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::table-describe-base[]
DESCRIBE TABLE cycling.race_times_summary;
// end::table-describe-base[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Described table."

tag="data-insert[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-insert[]
INSERT INTO cycling.race_times_summary (
  race_time, race_date
) VALUES (
  '10:01:18', '2017-04-14'
);

INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('10:15:20', '2017-04-14');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('11:15:38', '2017-04-14');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('12:15:40', '2017-04-14');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('10:01:18', '2018-07-26');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('10:15:20', '2018-07-26');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('11:15:38', '2018-07-26');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('12:15:40', '2018-07-26');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('10:01:18', '2019-03-21');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('10:15:20', '2019-03-21');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('11:15:38', '2019-03-21');
INSERT INTO cycling.race_times_summary (race_time, race_date) VALUES ('12:15:40', '2019-03-21');
// end::data-insert[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Insert data"

# Query all
tag="select-all[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-all[]
SELECT * FROM cycling.race_times_summary;
// end::select-all[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Select all"

# Query GROUP BY race_date
tag="select-group-by-race-date[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-group-by-race-date[]
SELECT race_date, race_time FROM cycling.race_times_summary
  GROUP BY race_date;
// end::select-group-by-race-date[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Select group by race_date"

# Query GROUP BY race_date, floor
tag="select-group-by-floor[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-group-by-floor[]
SELECT race_date, FLOOR(race_time, 1h), COUNT(*) FROM cycling.race_times_summary
  GROUP BY race_date, FLOOR(race_time, 1h);
// end::select-group-by-floor[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Select GROUP BY and FLOOR"